Technically you don’t need a calendar table to use Power Pivot, however in all but the most basic use cases you really should use one. Reasons to use a Power Pivot calendar table include:
- A calendar table allows you to use the filtering power of Power Pivot to make your reports fast and snappy.
- It allows you to filter your reports on attributes such as Year, Month, Quarter and any other aggregation of time you like.
- It allows you to create your own customer view of time that may be different to a standard calendar (such as a 4/4/5 calendar) and/or create your own definition of what is a financial year.
And indeed in some situations you simply must have a calendar table – specifically if you want to use the inbuilt time intelligence functions in Power Pivot (like year to date measures, same period prior year etc). Now there are other ways to create formulae that calculate time intelligence results other than the inbuilt functions, but generally the DAX is a bit more complex. More on that later.
Rules for a Calendar Table
Here are the mandatory rules of a date table if you want to use time intelligence functions. You don’t have to follow these rules if you don’t need/want to use the in built time intelligence functions, but it is still good practice particularly if your data is at a “day level” of granularity.
- You must have a date column in your calendar table
- The dates in this column must be in a contiguous range that covers the entire period of your data:
- no missing dates. It doesn’t matter if you don’t work weekends, you MUST include ALL DATES in the calendar table including weekends.
- no duplicate dates
- The calendar table must be marked as “date table” specifying the date column when asked in the dialogue box if you are using a non-date column. (This step is not required in Power BI as you can’t use a non-date key, only Excel).
- The date calendar reportedly needs to run through to the end of the last year used in the data model (although in my experience this makes no difference).
Note: It is not mandatory for your calendar table to be at the “day” level of granularity it could be week, month, hour, minute or what every else you want but you can’t use the inbuilt time intelligence functions unless it is at the “day” level.