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

image

 

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:

 

image

 

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:

 

image

 

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
Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s