Watch the video demonstration of how to create Power Pivot model that helps you create report not just on calendar month and year but also on financial month and year.
Introducing Date Table (Date Dimension)
In BI technology it is a common practice to have a separate role playing date dimension that is part of the cube. However, in case of Power Pivot, it might not be required at all in simple scenario. But as you move further and wants to review data across different timeline, a separate date table is required.
Please note that we cannot have role playing dimension in the Power Pivot. If you want to review 2 dates across different timeline, you need to add multiple date tables to the model.
What all columns it should have?
The date dimension table should have all the dates of the year on which your data is based. For example, in my sample, Order date minimum year is 2005 and maximum year is 2007. So my date dimension table should at least have all the dates for the year 2005 to 2007 and for subsequent year as well if data is expected to grow.
Apart from the dates, Year, Month Name, Quarter, Month Number, Quarter No etc. columns are also required in typical calendar dimension for each date.
If you also want your report to run on Fiscal dates, based on the country/organization’s financial year, Fiscal Month, Fiscal Year, Fiscal Month Number, and Fiscal Quarter etc. column values are also required with calendar year data. Since each country/organization have different financial year end, the date dimension table has to appropriately define those data. For example, in United States, financial Year starts is from July to June (June-Sep = Qt1 and April –June = Qt2) but in India, financial year is from April to March.
How to get the table?
Calendar year based date dimension is easy to find. The one I have used in demo is downloaded from Azure Marketplace and is free.
For date dimension that includes financial year data as well, a BI expert might already have its own that he/she uses across different projects, there are chances that you might need to create it if your financial year requirement is unique. Look around, chances are, you can find it ready made
In the demo above, I have the script that I run in SQL Server that fills date table for me and then I copy paste it in Excel for Power Pivot. This way it is easier for power users to review or modify it for granular analysis (weekday vs weekend, holidays etc.). For simple Power Pivot, it gets reused without any modifications.
The demo has calendar date dimension, I will delete that table, import new table with financial year details and fix existing measures which was referring to old date table.
For your learning, please download the Date Dimension Excel (Year 2000-2010) from BICG website here.