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.

PreFix	EnglishMonthName	Month
01-	January	01 –January
02-	February	02 –February
03-	March	03 –March
04-	April	04 – April
05-	May	05 –May
06-	June	06 –June
07-	July	07 –July
08-	August	08 – August
09-	September	09 –September
10-	October	10 –October
11-	November	11 –November
12-	December	12 – December

• 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.

PreFix	EnglishMonthName	Month
01-	July	01 –July
02-	August	02 –August
03-	September	03 –September
…	…	…

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.

Comments

• Anonymous
  April 21, 2010
  Not directly related to this article...Is it possible to calculate PERCENTILES or QUARTILES directy or by using any formula using GEMINI?We have a massive database and we need to calculate the distribution based on different parameters.
• Anonymous
  April 21, 2010
  Linking to the comment above...Could you put an article on this?That will be truly brilliant.
• Anonymous
  April 22, 2010
  You can find a blog post about Pareto and ABC analysis using PowerPivot here: http://sqlblog.com/blogs/marco_russo/archive/2010/01/19/abc-analysis-in-powerpivot.aspxThe technique is the same.Marco
• Anonymous
  July 14, 2012
  Why or why, in the name of everything holy isn't the month natural sorting not fixed; this has been outstanding since PPv1.0
• Anonymous
  August 13, 2012
  I agree with Max.  I thought this was going to be fixed in PowerPivot 2012.
• Anonymous
  April 06, 2013
  Using Power Pivot 2 is much easier.www.codeproject.com/.../Excel-2013-Power-Pivot-Sorting
• Anonymous
  December 20, 2013
  You can easily sort by natural month order in PowerPivot 2012.  Go to the PowerPivot source table, add a column and use this formula =FORMAT(Your date cell reference here,"MMM") to create a three letter abbreviation for the month.  Next make one more column and use this formula =MONTH(Your date cell reference here).  Then simply highlight the newly created abbreviated month column, select sort by column from the toolbar.  Then sort the month name by the month number.  Go to your Pivot Table and refresh, and your slicer will sort in the natural Jan-Dec order.  You may want to remove the month slicer prior to refresh, then add it back to the Pivot Table.  If you don't, sometimes it keeps it from auto sorting.  By no means is this convenient and something that should be a one click fix.  However, adding two columns is well worth avoiding tacky number prefixes and keeping your dashboard clean and professional.  Ps.  this also works for slicers in regular pivot tablesNice to know Microsoft's best "work around" is obvious, and not a solution to the problem.
• Anonymous
  January 01, 2014
  Worked Like Charm.........Thank You....
  • Anonymous
    August 03, 2016
    Another option: Select the month header cell of the pivot table. Right click and choose Sort>Custom Sort. Sort By your month column name. Sort On values. Under Order, select custom list. Add a new custom list in the natural Jan-Dec order. Click OK, OK. Refresh your pivot chart and the sliders will re-order to the custom list order.