If your Power BI project has more than one table with a date column or if you want to report on time periods other than year, month and quarter then you should create a calendar table. You may also see this referred to as a calendar or date dimension.
What is a date table?
A calendar table is a table of data with at least one column containing dates. This date column has to span across full years, it cannot contain blanks or have missing dates and all dates must be unique. For example, if your calendar table spans across 3 years it should have 1,095 rows, one row for each day. Typically a calendar table has additional columns such as year, month, quarter, day of week, weekend flag but it doesn’t have to.
Why do I need a date table?
There are three main reasons why you should create a calendar table in Power BI:
- A calendar table helps you report on any time period you need to. For example: to report on retail sales figures by day of week, view accounts by financial years, comparing sales this week to those the same week last year, to filter and slice data by weekdays and weekends etc.
- Power BI has some fancy time intelligence functions to help you manipulate data by different time periods and you need a calendar table defined to use these.
- A calendar table can be used as a link between different data sets in your report. For example, if you want to see if there is a correlation between the weather and number of litter tickets issued in a city, you would link these datasets via the calendar table.
How do I create a date table?
There are many different ways to create a calendar table in Power BI, I’ve created this easy-to-follow flow diagram to work out which is the best method for you.
Cover photo by Estée Janssens on Unsplash
BI Jo 