Sorting Month Names in DAX

I was recently helping a client set up their PowerPivot workbook for the first time and when they brought the months over on the pivot table, the results of the month weren’t sorted, so for example, April and August were the first months the list. Typically, users want to see months in sequential order (January, February, etc.). As DAX doesn’t have a key order by field, it’s a little bit different than in SSAS, where you can just order by the different key field. The solution is to create a separate column which is a concatenation of your Month number and month name fields so PowerPivot will display it in order.


For example, in your date dimension you have  a column called Month Number and a column called Month name



When you drag it over in excel, it’s completely out of order:


April 90
August 124
December 93
February 85
January 93
July 124
June 90
March 93
May 93
November 120
October 93
September 90


To sort it, add a column to the end of your PowerPivot workbook in the row context:




In the column, type up your formula. Concatenate the month number and the month number with a formula. In between the quotations, you can set it to be whatever you, or your business users, would like to see.


=[MonthNumberOfYear]& “-“&[EnglishMonthName]


Your new column will look like:




Right click and click rename, give your column a name, and go back to Excel. Drag over your new column in your PivotTable, and Voila! Months ordered in PowerPivot.


01-January 93
02-February 85
03-March 93
04-April 90
05-May 93
06-June 90
07-July 124
08-August 124
09-September 90
10-October 93
11-November 120
12-December 93