Figure 3 Chronological sequence of months in resulting Pivot
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:
.. 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.