A question from the Las Vegas IMA: How do you do date rollups in Excel when the company is on a fiscal year that does not end on December 31?
This video shows how to use Power Query to link to a Calendar table. The pivot table uses the Data Model and you create a relationship.
This video shows how to use Power Query to link to a Calendar table. The pivot table uses the Data Model and you create a relationship.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2452. Excel pivot table, fiscal year date rollups.
Hey, welcome back to the MrExcel netcast, I am Bill Jelen. Just last week I was doing a seminar for the Las Vegas chapter of the IMA and showing this trick that I show all the time with pivot tables.
How to take daily dates and roll them up to months and years.
So I put Date and Revenue in like that and then here under Group Field group to months and years.
And they do something like take the years field and drag it over to theCcolumns to create this great year-over-year report.
Ah, but then the question came in and said, well, that's great if you have a calendar fiscal year.
But what if your fiscal year ends on some other date, like for example, if your fiscal year ends March 31st?
Here's the way I'm going to do this.
I'm going to do this using the Data Model and some Power Query.
First thing I did is I created a Calendar table for my company that shows for every single date starting from 4/1/2020 going forward.
What fiscal year, what quarter, and what month.
You would put in the right text here to match your company. And then this is period one or month one.
But I like to put in text that it's April.
Anytime that I need to create a pivot table using the fiscal calendar, we’ll do these steps. Data, Get Data, From File, From Workbook.
Choose the Calendar table. Click Import, and Transform Data.
Over here on the far right hand side I want to give it a new name instead of Sheet1.
I'll call it Calendar, so we're good to go.
We're just going to do Close and Load, Close and Load To….
Add this data to the Data Model and Only Create a Connection. Click OK.
Alright, so what it does is that it creates in the data model our fiscal calendar.
And then here from this data I'm not even going to run this through Power Query.
We're just going to come here like a regular pivot table, Insert, Pivot Table.
With one difference, Add this Data to the Data Model, click OK.
And then here: Data, Relationships, New.
We're going to go from that table that we just imported – the Range.
It has a field called Date and link that to the Calendar table that has a field called Date and click OK.
Click Close.
Alright, so now that we’ve defined that relationship.
Over here in the Pivot Table Field list, we can choose “All” and in my data range I'll choose Revenue.
In my Calendar table, I will put fiscal years across the top, and Months down the side.
And we now have a report with each month along the left hand side and then the fiscal year.
So being able to join two tables in this case, a Calendar table and our Data table in order to allow those roll ups to happen.
Well, hey, the day that I'm recording is is the launch date for my new guide on the Retrieve platform.
This is 3 hours of Excel content. It's video content with a transcript.
And printed material to back it up.
What I love about Retrieve is you can search for anything and their Artificial Intelligence will find the exact spot in the video.
And then you can either watch that little snippet of video or just read the transcript.
It is a very fast way to find content in the course.
I love what they built there.
I have more courses coming next year but check out this Advanced Excel Techniques and also Cameo-style shout-out for your favorite person in the office in case you are looking for a last minute Christmas present.
I can create what looks like the MrExcel netcast with a shout out to them at the end.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let's hear you, Nancy.
Hey, welcome back to the MrExcel netcast, I am Bill Jelen. Just last week I was doing a seminar for the Las Vegas chapter of the IMA and showing this trick that I show all the time with pivot tables.
How to take daily dates and roll them up to months and years.
So I put Date and Revenue in like that and then here under Group Field group to months and years.
And they do something like take the years field and drag it over to theCcolumns to create this great year-over-year report.
Ah, but then the question came in and said, well, that's great if you have a calendar fiscal year.
But what if your fiscal year ends on some other date, like for example, if your fiscal year ends March 31st?
Here's the way I'm going to do this.
I'm going to do this using the Data Model and some Power Query.
First thing I did is I created a Calendar table for my company that shows for every single date starting from 4/1/2020 going forward.
What fiscal year, what quarter, and what month.
You would put in the right text here to match your company. And then this is period one or month one.
But I like to put in text that it's April.
Anytime that I need to create a pivot table using the fiscal calendar, we’ll do these steps. Data, Get Data, From File, From Workbook.
Choose the Calendar table. Click Import, and Transform Data.
Over here on the far right hand side I want to give it a new name instead of Sheet1.
I'll call it Calendar, so we're good to go.
We're just going to do Close and Load, Close and Load To….
Add this data to the Data Model and Only Create a Connection. Click OK.
Alright, so what it does is that it creates in the data model our fiscal calendar.
And then here from this data I'm not even going to run this through Power Query.
We're just going to come here like a regular pivot table, Insert, Pivot Table.
With one difference, Add this Data to the Data Model, click OK.
And then here: Data, Relationships, New.
We're going to go from that table that we just imported – the Range.
It has a field called Date and link that to the Calendar table that has a field called Date and click OK.
Click Close.
Alright, so now that we’ve defined that relationship.
Over here in the Pivot Table Field list, we can choose “All” and in my data range I'll choose Revenue.
In my Calendar table, I will put fiscal years across the top, and Months down the side.
And we now have a report with each month along the left hand side and then the fiscal year.
So being able to join two tables in this case, a Calendar table and our Data table in order to allow those roll ups to happen.
Well, hey, the day that I'm recording is is the launch date for my new guide on the Retrieve platform.
This is 3 hours of Excel content. It's video content with a transcript.
And printed material to back it up.
What I love about Retrieve is you can search for anything and their Artificial Intelligence will find the exact spot in the video.
And then you can either watch that little snippet of video or just read the transcript.
It is a very fast way to find content in the course.
I love what they built there.
I have more courses coming next year but check out this Advanced Excel Techniques and also Cameo-style shout-out for your favorite person in the office in case you are looking for a last minute Christmas present.
I can create what looks like the MrExcel netcast with a shout out to them at the end.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let's hear you, Nancy.
Last edited by a moderator:
