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.
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
- 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:
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?
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.
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:
- 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