The Calendar Table (Date Dimension) Explained for Power Pivot & Power BI

Bottom line: In this post we will learn what a Calendar Table or Date Dimension Table is, and how we can use it to create pivot table reports on multiple data sources.

Skill level: Intermediate

What is a Calendar Table (Date Dimension)?

The calendar table is a critical component to most Power Pivot models in Excel and Power BI models.  It can also be used with regular pivot tables.

I like to think of the calendar table as one of the “social butterflies” in the world of data table relationships.  You know those people at the office or party that seem to know everyone…

When we have a data model that contains two or more data tables (fact tables), then each of those tables is usually connected through a calendar table.  This allows us to create pivot table reports that compare data from multiple data sets over time.  The calendar table is a very powerful technique that can save us a lot of time.

*Calendar tables are also used in databases and data warehouses, and often referred to as Date Dimension Tables.

Download the File

Download the example Excel file to follow along.

What does a Calendar Table look like?

A calendar table typically contains a row for every date (day) that is in the data set.  So, if the data set contains data for one year, then the calendar table will have 365 rows (or 366 for leap years), one row for each day of the year.

The calendar table can span more than one year.  A calendar table that spans 10 years will have approximately 3,652 rows (10*365).

The first column of the calendar table contains a date for each day in each row.  In database terms this is called the primary key.  Each cell in the column must contain a unique value that is a date.

We will use this column for our lookup formulas or join relationships in Power Pivot or Power BI.

All of the other columns in the table contain metadata about the date in that row.  These metadata columns are used to group the dates into time periods for summary reports.

In the example above, column B groups the dates into years.  This means each row that contains a date in 2014 will contain the value 2014 in column B.

The same logic applies for the Month Name column (E).  Each cell in column E contains the month name for the date in the same row in column A.

Important Note:  It's important to remember that the Date column must contain a list of unique values, no duplicates.  The metadata columns that describe the date can contain duplicate values.  This is how the groups are created.

What can we use it for?

There are really two main uses for a calendar table.

Use #1 – Create Relationships Between Tables

Calendar tables are used to create relationships between data tables in Power Pivot and Power BI.  This allows us to create summary reports and dashboards from multiple data sources, AND compare numbers between those data sources.

A good example is actual versus budget reports.  Let's say we have a table of sales transaction data and another table of budget/forecast data.  We can use a calendar table in Power Pivot to create a relationship between these two tables, then create summary reports that include both data sets in the same pivot table.

The relationship between the tables in Power Pivot is a one-to-many join because there is one row for each date in the calendar table.  The data tables might contain multiple rows for the same date.

This relationship allows us to add fields to the Values area of the pivot table from both data tables, and compare the numbers by time period (month or year).

The calendar table is especially useful when our data sets have different time frequencies.  For example, the sales transaction data might be recorded by day & time, whereas the budget/forecast data is recorded by week or month.

We can use the calendar table to create reports that compare both data tables by the lowest data increment across both tables.  In this example the budget is recorded by month, so we will be able to compare sales revenue and budget down to the month level.  We can also go higher in the date groupings and compare by quarter or year.

Use #2 – Group Dates into Time Periods

Calendar tables are also used to group dates into time periods (years, quarters, months, weeks, days, etc.).  This works in Power Pivot and regular pivot tables.

We can think of a calendar table as a lookup table for date groupings.  We can use it to lookup a date, and return the year, quarter, month number, month name, week number, weekday name, etc. for the date.

In the image below I use a VLOOKUP formula in Column K of the Sales Data table to lookup the Order Date (column A) in the Calendar Table and return the value in the Wk of Month column.

There are many ways to go about this grouping in Excel including formulas with Date or Text functions, and the Group feature in a pivot table.  The calendar table allows us to more easily customize the groupings, especially for fiscal calendars.  I explain more in my articles on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data.  I have another article on 3 Ways to Group Times in Excel

We can also use Power Pivot to create this relationship, then use any of the columns from the calendar table in our pivot table report.  Checkout my article on how to use Power Pivot instead of VLOOKUP for details on this technique.

In the image above we group dates by the week of the month for each month in the year.  We can quickly change this report to sum revenue for any time period that is grouped in the calendar table.

How to Create a Calendar Table

There are a few ways to create a calendar table in Excel.  One of the easiest ways is to use Power Query (Get & Transform).  We can use a custom query to generate the entire table based on a start and end date.

The query will create the Date column (primary key) with a list of unique dates, and also create the additional metadata columns with the date groupings.

We can also use this same process in Power BI.  I explain this step-by-step in module 2 of my Power BI Dashboards and Data Course.  In the course you will learn how to create a calendar table for a fiscal year calendar, and compare data from Excel, Facebook, and the web.

What's Next?

In this post we learned what a calendar table is, and how it can be used in our data models.  It is an extremely powerful and useful technique for creating relationships between data sets.  We can group data by time periods and compare data from different tables in the same pivot table report.

In future posts I will explain how to further implement these techniques with Power Pivot.

Please leave a comment below with any questions.  Thank you! 🙂