When you run your data through PowerPivot, you can not group daily dates up to Months & Years as you can with a regular pivot table. Episode 1886 shows how to use a Calendar table to group and sort in Power Pivot. Check out the new Power Pivot Alchemy book at Power Pivot, PowerPivot, Excel, Excel 2010, Excel 2013, Microsoft Excel, Microsoft Power Pivot
Transcript of the video:
The MrExcel Podcast is sponsored Easy-XL.
Learning Excel from MrExcel Podcast 1886: Grouping & Sorting Power Pivot Dates.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
In the past back in version 1 of Power Pivot, I’ve talked about the fact that you cannot group dates that you run through Power Pivot so here we have a Date Field, if I would add this to the Data Model and then create a Pivot Table and put Dates along the left-hand side and Revenue in the Values area, you’ll see that there is no way on the Analyze or in Excel 2010 the Options tab to Group that field, to Group the daily dates up to months, quarters and years.
So, we have to do this ourselves and we do this through something called a Calendar Table.
I’m going to take Column C, our Date column, and COPY that Ctrl+C. I’ll create a new workshop and PASTE Ctrl+V. I want to remove duplicates, I need just a perfect unique data set here so we’ll go to Data, Remove Duplicates, click OK.
And then we’re going to add our own grouping field, so we’ll have Year, MonthNum, and MonthName.
I’m going to add MonthNumber even if I don’t need it so this is just straight Excel so =YEAR(A2), =MONTH(A2), and out here I would use =TEXT(A2, “MMM”), all right.
So there’s our formula.
You could, of course, add new columns for Quarter if you want to group by quarter or even Week if you want to group by week.
So, we add all of this data to our table.
Now, let’s make it into a table with Ctrl+T – My table has headers.
You don’t have to rename it but I’m going to call it Cal and we will add this to our Power Pivot Model.
So, you see when you have two worksheets: Table 1 and Cal, we need to create a relationship between those two, so I come back to Table 1 and I click on the Date Field and under Design, we will create a relationship from Table 1 Date to Calendar Date, click Create.
Simple enough.
I’m also going to come to the Calendar Table and on the Design tab, choose Mark as Date Table.
One more thing I’m going to do back on the Home tab, here under MonthName, one problem that we have in Power Pivot is that MonthNames are not sorted according to the custom list.
They’re sorted alphabetically, so April first and then August.
I’m going to say that I want to sort this column by the MonthNumber Column so even though I never intended to put the MonthNumber in the Pivot table, by adding that extra step, I’m now able to have the month names sort correctly.
Let’s go back to Excel, go to our Pivot table and I will remove this Date from the table and from my Calendar table, I’m going to add Year going across and Month Name going down.
You’ll see that I now have the month sorted properly and the year is going across.
Certainly, a lot easier to do this in regular Excel with just Group Field, right?
Group by Month, Group by Year and you can drag the fields across.
But it is possible to do that in Power Pivot using through the calendar.
Hey, and just a quick product note here, Rob Collie and my new book “Power Pivot Alchemy” is now out.
It’s available at mrexcel.com, amazon.com or anywhere you want to go.
Lots of great patterns and techniques for using Power Pivot Excel.
All right, hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learning Excel from MrExcel Podcast 1886: Grouping & Sorting Power Pivot Dates.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
In the past back in version 1 of Power Pivot, I’ve talked about the fact that you cannot group dates that you run through Power Pivot so here we have a Date Field, if I would add this to the Data Model and then create a Pivot Table and put Dates along the left-hand side and Revenue in the Values area, you’ll see that there is no way on the Analyze or in Excel 2010 the Options tab to Group that field, to Group the daily dates up to months, quarters and years.
So, we have to do this ourselves and we do this through something called a Calendar Table.
I’m going to take Column C, our Date column, and COPY that Ctrl+C. I’ll create a new workshop and PASTE Ctrl+V. I want to remove duplicates, I need just a perfect unique data set here so we’ll go to Data, Remove Duplicates, click OK.
And then we’re going to add our own grouping field, so we’ll have Year, MonthNum, and MonthName.
I’m going to add MonthNumber even if I don’t need it so this is just straight Excel so =YEAR(A2), =MONTH(A2), and out here I would use =TEXT(A2, “MMM”), all right.
So there’s our formula.
You could, of course, add new columns for Quarter if you want to group by quarter or even Week if you want to group by week.
So, we add all of this data to our table.
Now, let’s make it into a table with Ctrl+T – My table has headers.
You don’t have to rename it but I’m going to call it Cal and we will add this to our Power Pivot Model.
So, you see when you have two worksheets: Table 1 and Cal, we need to create a relationship between those two, so I come back to Table 1 and I click on the Date Field and under Design, we will create a relationship from Table 1 Date to Calendar Date, click Create.
Simple enough.
I’m also going to come to the Calendar Table and on the Design tab, choose Mark as Date Table.
One more thing I’m going to do back on the Home tab, here under MonthName, one problem that we have in Power Pivot is that MonthNames are not sorted according to the custom list.
They’re sorted alphabetically, so April first and then August.
I’m going to say that I want to sort this column by the MonthNumber Column so even though I never intended to put the MonthNumber in the Pivot table, by adding that extra step, I’m now able to have the month names sort correctly.
Let’s go back to Excel, go to our Pivot table and I will remove this Date from the table and from my Calendar table, I’m going to add Year going across and Month Name going down.
You’ll see that I now have the month sorted properly and the year is going across.
Certainly, a lot easier to do this in regular Excel with just Group Field, right?
Group by Month, Group by Year and you can drag the fields across.
But it is possible to do that in Power Pivot using through the calendar.
Hey, and just a quick product note here, Rob Collie and my new book “Power Pivot Alchemy” is now out.
It’s available at mrexcel.com, amazon.com or anywhere you want to go.
Lots of great patterns and techniques for using Power Pivot Excel.
All right, hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
