Calendar Reporting with Excel and Power Pivot
Imagine that you wanted to see a daily sales report for each day that also included month to date and year to date numbers. You could look at a large pivot table like this:
Or you could look at something that we are all a little more familiar with. A simple calendar:
In a calendar report, metrics can be laid out naturally so that they are easy for you to find. You can do a lot more with this layout than what I have shown above. However, let’s start with how we do the basics.
First we need to prep the date table in our Power Pivot model by making sure that we have the following columns:
Name |
Description |
One possible formula |
Day |
A continues date range of all days |
N/A |
Year |
Just the year portion of the date |
=Year([Day]) |
JustMonth |
The month without the year. This will be used for our month slicer values |
=FORMAT([Day],"mmmm") |
MonthSort |
Used to sort the JustMonth column so that is shows in the correct order in the slicer |
=MONTH([Day]) |
FirstDayOfMonth |
Shows the first day of each month |
=STARTOFMONTH('Date'[Day]) |
IntegerDay |
A copy of the day column where the datatype for the column is set to “whole number” |
=[Day] |
We also need to add one measure to our date table called SelectedMonth. This measure will be used to figure out which month was selected in the slicers and it will drive the rest of the values in the calendar. For this measure, we need to first check that only one year and one month are selected in the slicers and if so, display what that value is. We can use the following formula that uses the HASONEVALUE function to do our check: SelectedMonth:=IF(HASONEVALUE('Date'[FirstDayOfMonth]),VALUES('Date'[FirstDayOfMonth]))
Now that we have the model prepped we can start working on Excel. Below is a blank template of a calendar.
First let’s add the slicers for Year and JustMonth:
Now that we have our slicers we can select one year and one month and then we can pick a cell to display the value of the SelectedMonth measure that we created earlier. In this case, I have chosen cell C6:
I entered the following Excel formula:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[SelectedMonth]" ,Slicer_JustMonth,Slicer_Year)
The two parameters at the end, are the named ranges for the slicers that we added for year and month.
You can then merge and format the cell to get your desired look. When you change slicer values now you should be able to see the value in the firstdate range change.
Now we need to add a few more named ranges. More detail on the formula in these ranges can be found at: http://www.advanced-excel.com/excel_calendar.html. We can add these ranges by clicking on the “Formulas” tab and clicking “Name Manager.”
Add the following:
Name |
Refers to: |
days |
={1,2,3,4,5,6,7} |
weeks |
={0;1;2;3;4;5} |
daygrid |
=weeks*7+days-1 |
calendar |
=firstdate-(WEEKDAY(firstdate)-1)+daygrid |
Your name manager window should now look like this:
Now we have everything that we need to start filling in the calendar. If you go back to the template you will see that in cells A29 and A30 we have place holders for our measures. You can replace these two cells with the “CUBEMEMBER” formulas for the two measures that you want to use. In my example I used:
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sales Amount]","Sales")
And
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Units]","Units")
The next step is to start filling in the days on the calendar. Before we do that, you can add any additional slicers that you want to use for filters. In my example, I added one slicer for “Product Category.”
Let’s go to the first day on the calendar. It’s sometimes helpful to pick a month that starts on a Sunday when doing this for the first time.
Each formula that we use from this point forward will start with: =IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",….
We are checking to see if the date that is in the selected index is in the same month as the month that is selected in the slicers. If it is not, we return blank. Here is a breakdown of all the formulas.
Cell |
Description |
One possible formula |
C10 |
Gets the cubemember for the current integer day. I have set the text color to white so that users do not see this value |
=IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",CUBEMEMBER("ThisWorkbookDataModel","[Date].[IntegerDay].&["&INDEX(calendar,$A10,D$8)&"]",INDEX(calendar,$A10,D$8))) |
D10 |
Gets the current day from the calendar range based off the index values in cells D8 and A10. You can format this cell to only show the day by setting the number format to “d” |
=IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",INDEX(calendar,$A10,D$8)) |
C11 |
References our first measure CUBEMEMBER in cell $A$29 |
=IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",$A$29) |
D11 |
References our second measure CUBEMEMBER in cell $A$30 |
=IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",$A$30) |
C12 |
Looks up the cube value, passing in the cube members in C10 and C11 as well as the Slicer_Product |
=IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",CUBEVALUE("ThisWorkbookDataModel",Slicer_Product,C10,C11)) |
D12 |
Looks up the cube value, passing in the cube members in C10 and D11 as well as the Slicer_Product |
=IF(MONTH(firstdate)<>MONTH(INDEX(calendar,$A10,D$8)),"",CUBEVALUE("ThisWorkbookDataModel",Slicer_Product,C10,D11)) |
Now that the first day is complete, we just need to copy all these values into the remaining days. The last step is to add any formatting and additional values that you like and we are finished.
Template and full workbook are attached at the bottom of this page. For another example, check out: http://www.powerpivotpro.com/2012/08/introducing-the-calendar-chart/