PowerPivot Model: Why am I not seeing “Month Names” in correct logical order?


This blog post is for people who have seen the reports built on PowerPivot model where the Month Names are not in correct logical order. So instead of  “January, February, March, April …” (which is correct logical order), the order in the report would be displayed as “April, February, January, March..” (which is NOT correct).

This is what I am talking about:

powerpivot model month name not sorted correctly

Here, Month names are not sorted correctly, right? So how do we solve this? Let’s see this in this blog post!

Understanding the Sample Data-set

Optional: Download a sample data-set to practice what’s described in this blog-post: Download – Paras Doshi Blog’s sample data set

Now the data looks like this:

Date Daily New number Month Month Name
1/1/2012 0:00 0 1 January
1/2/2012 0:00 0 1 January
1/3/2012 0:00 0 1 January
1/4/2012 0:00 0 1 January
1/5/2012 0:00 0 1 January
1/6/2012 0:00 0 1 January
1/7/2012 0:00 0 1 January
….. …… …… ……

Note that “Daily new number” is used for the demo purpose. I had to anonymize the data before I could share it with you all!

Originally, the table had just two columns but since I wanted to add “time Intelligence” to the model. I added two columns Month and Month Name.

Now here’s the DAX behind these two columns:

Column DAX
Month MONTH(‘Sample Table'[Date])
Month Name RELATED(Month[MonthName])

There’s a relationship between Month Column of “Sample Table” and Month column of “Month” table

And here’s what Month Table looks like:

Month MonthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December

Creating a Report on Top of Sample Data-set.

Now if you create the report of Month Name vs Daily New Number, it will look like:

powerpivot model month name not sorted correctly

Oops, Problem detected: Month name are not in correct order.

So now let’s solve it.

Solution to sort month name correctly

Let’s solve this issue in our PowerPivot Model. Here’s what you do:

1. Go to PowerPivot model, Select “Month Name” column from the sample table

2 Toolbar > Home > Sort By column > click on “Sort by column”

3. And set the properties in the dialog box as:

powerpivot model sort by column dialog box

Now, go back to the report and refresh the connection to the model.


powerpivot model month name are sorted correctly

This is so because by default since the month name is “Text” – it was sorted in A to Z format. But you saw how we can use the sort by column property in PowerPivot model to fix this issue.

That’s about it for the post! your feedback is welcome!

Want to Read More? Here are few links:

3 thoughts on “PowerPivot Model: Why am I not seeing “Month Names” in correct logical order?

What do you think? Leave a comment below.