What is a date table and why should I create one in my Power BI project?

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:

  1. 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. 
  2. 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.
  3. 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.
Explains how to connect the date table to other tables in Power BI

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.

Processing…
Success! You're on the list.

Cover photo by Estée Janssens on Unsplash

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: