One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.

In short, I was never happy with my PivotTables when they ended up looking like this:

Slicers had similar issues as well:

Excel 2010 & PowerPivot 2008 Solution:

To counter this, we pretty much had to add a column of data to our tables that converted our months into the mm-mmm format, which mean that they would show up as 01-Jan, 02-Feb, etc… Doing this would put the PivotTable columns and slicer buttons in the correct order.

Excel 2010 & PowerPivot 2012/Excel 2013+ Solution:

Fortunately, PowerPivot 2012 gave us a way to sort our columns so that they show up correctly in our PivotTables and slicers. Here’s how.

Example 1: Sorting Months

In the PivotTable pictured above, the “Month_Short” column is out of order. To fix this, we enter PowerPivot and pull up the table that has our dates:

Next we:

• Click somewhere in the “Month_Short” column
• On the PowerPivot Home Tab, click “Sort by Column”

Now we need to figure out which column to sort by. In this case, we want to sort by the “Month_Num” column. January will therefore sort as 1, February as 2, etc:

At this point, we can click OK, go back to Excel and refresh our PivotTable:

Much better! And it works for Slicers too:

Example 2: Sorting Weekdays

Now, what if we wanted to add weekdays to the mix? Can we have more than one sort per table? Of course!
We’d head back in to PowerPivot, find the “Day_Short” column and choose to “Sort by Column” again:

Now, the question is, which column do we sort our Day_Short column by? Logically, you’d think it would be Day_Num, as that has the two digit date you’re trying to sort, right?

If you try that, however, Excel will throw an error for you:

So what does that mean?

Basically, what it means is that the relationship between the “Day_Short” and whatever it is being sorted by must be a one-to-one relationship. In the case of Day_Num, Excel is complaining because Sunday could be 28 (Jan), 3 (Feb), 10 (Feb), 17 (Feb), etc… Monday could be 29 (Jan), 4 (Feb), etc… In short, Excel has too many options to sort, so it can’t figure it out.

What Excel is looking for is something very clear and consistent to work with. So how do we make it sort as follows?

• 1 Sun
• 2 Mon
• 3 Tue
• 4 Wed
• 5 Thu
• 6 Fri
• 7 Sun

Well… conveniently, these just happen to be the numbers should in the Weekday_Num column! Every Sunday has a corresponding value of 1, Monday = 2, etc… So let’s sort by that one.

At this point we can return to Excel, Refresh the model, and drop the “Day_Short” onto the PivotTable. Notice that it also sorts correctly:

Try this yourself:

If you’d like to replicate the above:

• You must be running Excel 2010 and PowerPivot 2012
• Download the sample database
• Create a new Excel file and open PowerPivot
• Link to the following tables in the Access database:
  • tblDates
  • qryCOA
  • qryTransactions

• Create the following relationships:
  • qryTransactions [TranDate] --> tblDates [Key_date]
  • qryTransactions [Link_AccDept] --> qryCOA [Link_AccDept]

• Create a new PivotTable as follows:

• Using the slicer, restrict your data to Dept 150 for 2009