Sorting months chronologically and not alphabetically in a Power Pivot based Pivot Table
Here is our “problem”. When you create a Pivot Table in Excel that has a date field and is based on data in Excel, you can Group that field by month and the sort will be chronological (January, February, …). But when you create a Pivot Table based on Power Pivot, grouping does not work! (This can also happen if no Power Pivot is used). Within a Power Pivot based Pivot Table, there is no Pivot Table setting that will sort months chronologically. Sorting Months Chronologically will require you to do some extra work, but you also gain much more flexibility!
Suppose you find it easier to learn through video. In that case, you can find the video tutorial for sorting months chronologically and not alphabetically on my YouTube channel: Click Here to View Video on YouTube or watch the video embedded at the end of this post.
Notice that I put the word problem in the first sentence in double quotes. The reason being that it’s really not a problem. It’s one of those – “it’s not a bug it’s a feature” things. Also, this feature does enable you to do much more than you’re used to!
Sorting Months Chronologically (the short version)
To Sort Months Chronologically, you need a Power Pivot Data Model and a Date field. Next you should get Month names and Month numbers by using Formulas. Finally you can then use Sort By Column in Power Pivot. To get the month names from Dates we use the Format function in Power Pivot (it’s the Power Pivot equivalent of the Text function in Excel, both are explained further down this post), 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 not what we are used to when it comes to Pivot Tables. This article will tell you how to get the chronological sorting in Excel (Case 1) and how to do it in Power Pivot (Case 2).
For both cases, we will use a simple Excel Table. It contains only two columns. Date values are in one, 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.
First let’s do it in Excel
Case 1: Pivot Table report based on an Excel Table
Select any cell within a table and go to Insert/Pivot Table
The Pivot Table will show the number of visitors by months. But to do this, since we only have Dates, we have to Group Dates into Months. Right click on any Date and select Group.
And right away we get the desired result.
If that was not the case, we would get the right sort order by sorting those by Custom Lists. More Sort Options/More Options remove AutoSort and select First key of Sort Order…
Now let’s take the same data, but create a Pivot Table based on Power Pivot.
Case 2: Pivot Table report based on Power Pivot data.
First we add our Table data to Power Pivot the easiest way we can, by using the Add to Data Model command on the Power Pivot tab.
This gets the data into Power Pivot. Next, let’s create a Pivot Table. Simply by choosing Pivot Table Report from the Power Pivot window. Recreating the “Excel” Pivot Table, we want to group dates by months and we just can’t select the Group command. It is grayed out…
To get the month names, 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 Power Pivot version of the Excel’s Text function. The syntax is the same, just the names differ. Compare the functions bellow.
Excel
Power Pivot
Using the Format function we get the month names. This also gives us a new field in a Pivot Table. However, adding Month Name to Rows looks quite disappointing.
The numbers are OK, but the sorting is alphabetical. We want to be sorting months chronologically. To get the sorting right, we have to go back to the Power Pivot window and create a new Calculated column using the Month function. This gives us a Month Number along with Date and Month Name.
Now simply replacing the Month Names by Month Numbers in 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 with the correct sorting. Luckily at this point we have everything we need. Also, we are just one step away from eternal happiness.
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 Number column.
Doing that has changed our Pivot Report instantly! And that is how you go about sorting months chronologically!
Conclusion
Wrapping up, think about this scenario. You want to sort the Month Names so that the first month would be July and the last would be June. Fiscal year sort of thing. You just add an Index column where July is 1 and June 12. Next you choose to sort Column Names by this new index using the Sort By Column command and you’re done!
Here is a video from our YouTube Channel depicting this technique:
More about Power Pivot here and here. If you want to see all the differences between a Power Pivot based Pivot Table and a normal Excel Pivot Table, you can read about it here.
Related Posts
- October 14, 2014
Probably the longest title of all times, but it leads to ...
- September 30, 2014
If some pictures are hard to view, you can get the PDF of the ...
- August 19, 2014
One might think that analyzing data with a Pivot table is hard, ...
- May 16, 2014
This is the second article of three. If you hadn’t read the ...
Reblogged this on SutoCom Solutions.
Your article is really good to read. Amazing!!
Thanks for sharing, could I post it on my Facebook to share with my friends?
Thanks for the compliments. You can, the link is at the bottom of the article… Just click the Facebook button.
Howdy! I just wish to give you a huge thumbs up for the excellent info you have here on this post.
I’ll be coming back to your blog for more soon.
Hello, I enjoy reading through your article.
I wanted to write a little comment to support you.
This article is in fact a good one it helps new net visitors, who are wishing in favor of blogging.
Very good information. Lucky me I ran across your site by chance (stumbleupon).
I’ve book marked it for later!
This paragraph will assist the internet viewers for creating new blog or even a
weblog from start to end.
Spot on with this write-up, I actually think this website needs
a great deal more attention. I’ll probably be back again to see more, thanks for the advice!
Please let me know if you’re looking for a article writer for your
blog. You have some really great articles and I believe I would be a
good asset. If you ever want to take some of the load off,
I’d love to write some articles for your blog in exchange for a link back to mine.
Please send me an email if interested. Cheers!
Hi,
as a matter of fact, I would give great praise if you were to contribute to Excel Unplugged and if approved you can link to your blog as much as you like but overall there would be some conditions.
– As I don’t know you and your writing style, in the beginning your contributions would have to be approved by me (just until the trust is established).
– since the blog is Excel Unplugged all contributions would have to be Excel related and at least intermediate by level.
– If this sounds good to you and to anyone else, you can reach me through the contact portion on my blog. You can also give your blog in the website field, so I can check it out…
I do not know whether it’s just me or if everyone else experiencing problems with your site.
It appears like some of the written text in your posts are running off
the screen. Can someone else please comment and let me
know if this is happening to them as well? This might be a issue with my web browser because I’ve had this happen before.
Thank you
Since this is the first mention of overflow on this site, the conclusion has to be that it’s a problem with your browser. Try a different browser and let me know if it still doesn’t work.
Thanks in favor of sharing such a pleasant opinion, post is pleasant, thats
why i have read it completely
I must thank you for the efforts you have put in writing this
website. I’m hoping to see the same high-grade content by you in the future as well.
In truth, your creative writing abilities has inspired me to get my own site now 😉
hello there and thank you for your info – I’ve certainly picked up anything new from right
here. I did however expertise some technical points using this web
site, as I experienced to reload the web site lots of times previous to I could get it to load properly.
I had been wondering if your hosting is OK? Not that I am complaining,
but sluggish loading instances times will very frequently affect your placement in google and could damage your
quality score if ads and marketing with Adwords.
Anyway I am adding this RSS to my e-mail and can look out
for a lot more of your respective interesting content.
Ensure that you update this again very soon.
We stumbled over here coming from a different web address and thought I should check things out.
I like what I see so now i’m following you. Look forward to going over your web page for a second time.
Hello there, You have done an excellent job.
I will certainly digg it and personally recommend to my friends.
I’m confident they will be benefited from this website.
After going over a handful of the articles on your blog, I really like your technique of writing a blog.
I book marked it to my bookmark site list and will be checking back in the near future.
Please check out my web site too and let me know what you think.
Pretty! This was a really wonderful article. Many thanks
for providing this info.
Thanks in support of sharing such a good idea, article is nice, thats why
i have read it fully
Very good written post. It will be beneficial to anyone who
usess it, as well as me. Keep up the good work – looking
forward to more posts.
Thanks Gasper. You have saved my day.
Hi Gasper,
Thanks for the tips and tricks!
With respect to this article I have one additional question. What if I want to work with multiple years. How can I make sure that the value for January 2016 come after December 2015 and is not added to January 2015. I want to look 12 months back but with a dataset which contains multiple years I end up with a chart where January from one year is added up to the January of a previous year.
I hope you have a solution for this.
Thanks in advance
Hi Manfred,
your best option would be to create a calculated field YYYYMM so 201609 that would relate to September 2016… Or any other format you like, but it has to include month and year information.
There are some days I need that answer. Thanks so much!