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.
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:
This can be seen from the following image:
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:
Figure 2 Sort menu in Excel
The months are now sorted in chronological order. This can be seen from the image below:
Figure 3 Chronological sequence of months in resulting Pivot
table
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:
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.
Jen Stirrup 
Excellent…Thanx jen…