Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
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:
One possible formula
A continues date range of all days
Just the year portion of the date
The month without the year. This will be used for our month slicer values
Used to sort the JustMonth column so that is shows in the correct order in the slicer
Shows the first day of each month
A copy of the day column where the datatype for the column is set to “whole number”
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:
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:
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:
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.
Gets the cubemember for the current integer day. I have set the text color to white so that users do not see this value
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”
References our first measure CUBEMEMBER in cell $A$29
References our second measure CUBEMEMBER in cell $A$30
Looks up the cube value, passing in the cube members in C10 and C11 as well as the Slicer_Product
Looks up the cube value, passing in the cube members in C10 and D11 as well as the Slicer_Product
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/
This is great, thx for sharing
Can you post a 2010 compatible version?
.TY for sharing Josh!
On last step of calculation get #N/A error for "IntegerDay" cell (e.g. C10). Will this not work with Excel 2010?
OMG - Very Smart _ I like it so much _Thank you many Thanks
Excellent post... Thanks for sharing!