Sorting months chronologically and not alphabetically in a Pivot Table report based on Power Pivot data

Here's our problem. When you create a Pivot Table in Excel, you can Group that field by month and the sort will be logical (January, February, …). But when you create a Pivot Table based on Power Pivot, the grouping does not work! So you have to get to the month names by a different road. We do this by a Format function in PowerPivot, but the problem is that when you put this field in a Pivot Table, it gets sorted alphabetically. This is logical since the values are text and have nothing to do with dates as far as that Pivot Table is concerned, but this is a problem since the months are not sorted chronologically. This article will tell you how to achieve that.
Let’s start with a simple table in Excel that has only two columns. One has Date values and the other has a number of visitors on that date. Now we would like to create a Pivot Table report to see how the number of visitors is spread through the months.

Case 1: Pivot Table report based on an Excel Table

First we create a Pivot Table based on an Excel Table
The Pivot Table will show the number of visitors by months. But to do this, since we only have Dates, we have to do Grouping by months on the Dates
And right away we get the desired result.
 

Case 2: Pivot Table report based on Power Pivot data

First we add our Table data to Power Pivot the easiest way – by using the Add to Data Model command on the PowerPivot tab.
Now that we have the data in the Power Pivot we can create a Pivot Table report from Power Pivot window. But when we create a Pivot Table and want to see the analysis by months we see we just can’t select the Group command. It is grayed out…
So to get to months we use a different trick, we go back to the Power Pivot window and create a calculated column using a Format function. This is a PowerPivot rendition of the Text function from Excel. The syntax is the same, just the names differ.
Excel Version
  Power Pivot version.
Using the Format function we now get the month names and a new field to create a Pivot Report by. But when we create it, it looks quite disappointing.
  So the numbers are OK, but the sorting is alphabetical and not the kind we want. To get the sorting right, we have to go back to the PowerPivot window and create a new calculated column using the Month function. This way we now get a month number along each date and month name.
Now just adding that to the Pivot Table report would get rid of our problem, but let’s not forget that we want the month names as they were, only the sorting is wrong. But now we have all we need.
  In the Power Pivot window, we select a value in the month name column and then select a Sort by Column command on the home tab and hey, look at that. You can now say that the Month name column will be sorted by Month No. column.
  Doing that has changed our Pivot Report instantly
  And we are one step closer to eternal happiness.