Sorting month by natural sort order

When a field containing month names is added to a PivotTable or to a Slicer the field is sorted in alphabetical order (April, August, etc.) by default. As you may already have discovered this behavior can easily be changed for the PivotTable allowing you to sort the month in calendar order. The workaround for the Slicers is less obvious however. This post describes how you can naturally sort month fields in calendar order.

Sorting month by natural sort order in the Pivot Table

Changing the sort order of the month name when the field is added to either row or column labels of a PivotTable is relatively easy: You can simply leverage Excels auto-sort functionality as shown below, where ascending sort order for the month row label will sort in calendar order.

Note that this approach is only applicable when the language of your month names in PowerPivot matches the selected Excel language. If the languages do not match the month names will not be recognized by Excel and the sort change will take no effect.

Sorting month by natural sort order for Slicers

When the month name field is added to Slicers, the Excel auto-sort functionality is not applicable. So here an alternative approach is needed.

One approach is to create an additional month column that prefaces the month name with month number. This can be done as follows:

• Create an additional month table in either Word or Excel like the one below. The table should contain the following:
  • A numeric ‘Prefix’ column, which will be used to ensure the proper ascending sort order
  • A ‘Month Name’ column containing unique month names identical to the names used in the ‘Month Name’ column in your original date table.
  • A concatenated ‘Month’ column, which is the column that will be used for representing the new month field that can be appropriately sorted when added to a Slicer. This column can either be created as part of this initial preparation table as shown below or can be created as a calculated column (=CONCATENATE([PreFix],[EnglishMonthName])) once the table has been added to PowerPivot
• Copy the table and paste it as a new table into the PowerPivot window.

• Create a relationship between the EnglishMonthName column of your original date table and the EnglishMonthName column of the pasted prefix table
• In the original date table, add a new column referencing the concatenated month column of the pasted prefix table using DAX [formula: =RELATED(Prefix[Month])]
• This new DAX column can now be added to your Slicers representing Month, which will sorted in calendar order

Hiding the prefix table

To ensure a more user friendly experience you may want to hide the pasted prefix table from the Field List since it is serves no purpose for the end user. To hide the table you simply hide all the columns from the PivotTable, which is done via the hide option in the PowerPivot window (Design | Hide & Unhide | Uncheck Select All from the PivotTable option)

Note that the same approach can be used to sort by fiscal year by simply creating a similar prefix table representing fiscal sort order.

Summary

This concludes the walkthrough of how you can naturally sort month fields in calendar [or fiscal] order. It should be mentioned that this in an area we are looking to ease in the next version. In the meantime we hope this will be helpful.