Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
When a field containing month names is added to a PivotTable or to a Slicer the field is sorted in alphabetical order (April, August, etc.) by default. As you may already have discovered this behavior can easily be changed for the PivotTable allowing you to sort the month in calendar order. The workaround for the Slicers is less obvious however. This post describes how you can naturally sort month fields in calendar order.
Sorting month by natural sort order in the Pivot Table
Changing the sort order of the month name when the field is added to either row or column labels of a PivotTable is relatively easy: You can simply leverage Excels auto-sort functionality as shown below, where ascending sort order for the month row label will sort in calendar order.
Note that this approach is only applicable when the language of your month names in PowerPivot matches the selected Excel language. If the languages do not match the month names will not be recognized by Excel and the sort change will take no effect.
Sorting month by natural sort order for Slicers
When the month name field is added to Slicers, the Excel auto-sort functionality is not applicable. So here an alternative approach is needed.
One approach is to create an additional month column that prefaces the month name with month number. This can be done as follows:
04 – April
08 – August
12 – December
Hiding the prefix table
To ensure a more user friendly experience you may want to hide the pasted prefix table from the Field List since it is serves no purpose for the end user. To hide the table you simply hide all the columns from the PivotTable, which is done via the hide option in the PowerPivot window (Design | Hide & Unhide | Uncheck Select All from the PivotTable option)
Note that the same approach can be used to sort by fiscal year by simply creating a similar prefix table representing fiscal sort order.
This concludes the walkthrough of how you can naturally sort month fields in calendar [or fiscal] order. It should be mentioned that this in an area we are looking to ease in the next version. In the meantime we hope this will be helpful.
Not directly related to this article...
Is it possible to calculate PERCENTILES or QUARTILES directy or by using any formula using GEMINI?
We have a massive database and we need to calculate the distribution based on different parameters.
Linking to the comment above...
Could you put an article on this?
That will be truly brilliant.
You can find a blog post about Pareto and ABC analysis using PowerPivot here: http://sqlblog.com/blogs/marco_russo/archive/2010/01/19/abc-analysis-in-powerpivot.aspx
The technique is the same.
Why or why, in the name of everything holy isn't the month natural sorting not fixed; this has been outstanding since PPv1.0
I agree with Max. I thought this was going to be fixed in PowerPivot 2012.
Using Power Pivot 2 is much easier.
You can easily sort by natural month order in PowerPivot 2012. Go to the PowerPivot source table, add a column and use this formula =FORMAT(Your date cell reference here,"MMM") to create a three letter abbreviation for the month. Next make one more column and use this formula =MONTH(Your date cell reference here).
Then simply highlight the newly created abbreviated month column, select sort by column from the toolbar. Then sort the month name by the month number. Go to your Pivot Table and refresh, and your slicer will sort in the natural Jan-Dec order. You may want to remove the month slicer prior to refresh, then add it back to the Pivot Table. If you don't, sometimes it keeps it from auto sorting. By no means is this convenient and something that should be a one click fix. However, adding two columns is well worth avoiding tacky number prefixes and keeping your dashboard clean and professional.
Ps. this also works for slicers in regular pivot tables
Nice to know Microsoft's best "work around" is obvious, and not a solution to the problem.
Worked Like Charm.........Thank You....