Sorting month by natural sort order

Sorting month by natural sort order

  • Comments 8

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.

clip_image001[4]

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.

clip_image002[4]

One approach is to create an additional month column that prefaces the month name with month number. This can be done as follows:

  • Create an additional month table in either Word or Excel like the one below. The table should contain the following:
    • A numeric ‘Prefix’ column, which will be used to ensure the proper ascending sort order
    • A ‘Month Name’ column containing unique month names identical to the names used in the ‘Month Name’ column in your original date table.
    • A concatenated ‘Month’ column, which is the column that will be used for representing the new month field that can be appropriately sorted when added to a Slicer. This column can either be created as part of this initial preparation table as shown below or can be created as a calculated column (=CONCATENATE([PreFix],[EnglishMonthName])) once the table has been added to PowerPivot
  • Copy the table and paste it as a new table into the PowerPivot window.

PreFix

EnglishMonthName

Month

01-

January

01 –January

02-

February

02 –February

03-

March

03 –March

04-

April

04 – April

05-

May

05 –May

06-

June

06 –June

07-

July

07 –July

08-

August

08 – August

09-

September

09 –September

10-

October

10 –October

11-

November

11 –November

12-

December

12 – December

  • Create a relationship between the EnglishMonthName column of your original date table and the EnglishMonthName column of the pasted prefix table
  • In the original date table, add a new column referencing the concatenated month column of the pasted prefix table using DAX [formula: =RELATED(Prefix[Month])]
  • This new DAX column can now be added to your Slicers representing Month, which will sorted in calendar order

clip_image003[4]

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.

PreFix

EnglishMonthName

Month

01-

July

01 –July

02-

August

02 –August

03-

September

03 –September

Summary

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.

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
  • 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.

    Marco

  • 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.

    www.codeproject.com/.../Excel-2013-Power-Pivot-Sorting

  • 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....

Page 1 of 1 (8 items)