Sorting Months from PowerPivot models in Excel Filters and Slicers

Date Sorting by month requires additional steps for Excel Filters and Slicers, and these will be discussed here. The excellent book entitled ‘Practical PowerPivot and DAX Formulas for Excel 2010’ by Tennick suggests one method of fixing the issues around the Excel slicer sorting, which involves an IF statements on an existing Month Number column to obtain the Month number, and sort by that. This blog offers an alternative way of dealing with the sorting issues involved with Excel Slicers. Here is a different approach, which is designed to promote re-use by generating a separate Month Number column from the Date 
Key.

Date Sorting by Month for Excel Filters

When Excel retrieves the month from a PowerPivot model, the months are sorted in alphabetical rather than chronological order. 
This can be seen from the following image:

1. Initial Incorrect Date Sorting in Filters


Figure 1 Date Sorting by Month in Alphabetical Order

In order to sort by chronological order, additional steps are required:

  • The user needs to click on the arrow next to Row labels, and select the option ‘Sort A to Z’


This can be seen from the following diagram:

2. Sorting dates in Filters

Figure 2 Sort menu in Excel

The months are now sorted in chronological order. This can be seen from the image below:

3. Correctly sorted dates in Excel Filter


Figure 3 Chronological sequence of months in resulting Pivot 
table


Date Sorting by Month for Excel Slicers

The ‘Month Number’ attribute may not be able to be used because there is no leading ‘zero’ in order to assist in the sorting process. This may result in strange sorting, if the Month Number is interpreted as a string e.g. 1, 10, 11, 12, 2 and so on.
In order to obtain ‘Month Number’, the following calculated field called ‘Month Number Extract’ was created on the Date Dimension:
=RIGHT(LEFT(‘Date Dimension Query'[Date As Integer], 6), 2)
Therefore, if the date key is in the format YYYYMMDD, then the above calculation takes out the ‘MM’ piece. For example, if the date is 11th February, 2011, then the key looks like: 20110211 and the calculation would take out ‘02’.
Since the calculation already has the leading zero prefixed, there’s no need to add it in again. Also, by putting this field into a calculated column, it is potentially reusable by other formulae if required.
Once the Month Number has been obtained, it is then possible to prefix the Month Name so that it is sorted correctly.  In other words, instead of getting the alphabetical ordering of months, e.g. April, August, December, and so on, the user is presented with a nice chronological ordering of months e.g. January, February, March and so on.
In order to arrange the prefixed month, the calculated column will appear as follows:
=’Date Dimension Query'[Month Number Extract] & “-” & ‘Date Dimension Query'[Policy Inception DateYQMDMonth]
This results in a calculated column which returns month names with prefixed month number orderings as follows:
01-January
02-February
.. and so on.
When an Excel Pivot table has a Slicer added to it, then the new Slicer appears as follows:

4. Final Slicers

The months are nicely sequenced, but it is unfortunate that the month ordering element is still visible. According to the book entitled ‘Practical PowerPivot and DAX Formulas for Excel 2010’ by Tennick, the Excel slicer sorting issue will be fixed in the next release of PowerPivot.
In the meantime, this workaround will help to ensure that the months are sorted properly in a way that is intuitive to the users.

One thought on “Sorting Months from PowerPivot models in Excel Filters and Slicers

Leave a Reply