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

2 thoughts on “Sorting Month Names in DAX”

    1. Very true J! Back in 2012 when I wrote this that functionality didn’t exist, so this was the only workaround at the time. Now you can very easily sort by clicking on Sort By Column and selecting the integer based column.

      Thanks for the tip!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s