The Calendar Table (Date Dimension) Explained for Power Pivot & Power BI - Excel Campus
13

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

The Calendar Table Explained

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…

Calendar Table - Date Dimension - Connected to Data Tables in Power Pivot

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.

Calendar Table Explained.xlsx (669.3 KB)

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.

The Components of a Calendar Table - Date Dimension

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.

Calendar Table Connected to Data Tables with Date Column

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.

Calendar Table One to Many Join Relationship in Power Pivot

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).

Daily Sales vs Monthly Budget Pivot Table Power Pivot- Calendar Table

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.

Daily Sales vs Monthly Budget Data Tables

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.

VLOOKUP to Return Week Number to from Calendar Table to Sales Table

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.

Calendar Table with Power Pivot to Create Reports by Various Time Periods

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.

Here is a list of some great resources for these calendar table queries.

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.

Power BI Dashboards & Data Course Logo 557x316

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 13 comments
Mickael - July 13, 2017

Jon,

Thank you for the tip and all your work. I visit your website religiously.

I don’t understand your first tip. Pivot table functionality allows you to group dates by year, quarters, months, days, intervals… Why would I need the relationship?

Reply
    Jon Acampora - July 14, 2017

    Hi Mickael,

    The calendar table allows us to create relationships between multiple tables. The date grouping feature of a pivot table will only group dates based on one data source.

    If you wanted to bring data from multiple tables into one pivot table for comparison reports, and group the dates, then you need a calendar table to create that relationship between both tables.

    I hope that helps. Thanks!

    Reply
Bob Powers - July 7, 2017

Hello Jon,
This is exciting. I am working on a budget/forecasting program along with expenditures related to each budget line item. I just took a cursory look at your email but it appears this would be a good fit for presentation when I get further along with VBA Pro and the other things currently in process.

Jon, are you ever a FIND for me!! As I have said previously, the Lord has blessed me with many things and you are the most recent.

Bob

Reply
    Jon Acampora - July 14, 2017

    Thank you Bob! I really appreciate your nice feedback and feel blessed to have awesome supporters like you. 🙂

    Reply
Jose - July 7, 2017

Not to be picky, but last I recall, there are 365 days is a year (366 in a leap year). Good article though.

Reply
    Jon Acampora - July 14, 2017

    Thanks for letting me know Jose! That was a typo on my part. No wonder I feel like I’m getting old so fast… I was missing a day. 🙂

    Reply
intekhab alam - July 6, 2017

Dear Team,

Thanks for support. but i want more help from you.
i want support for advance filter and vba

Reply
steve flood - July 6, 2017

Very interesting, thanks – but you should be doing something better with your 4th July

Reply
    Jon Acampora - July 6, 2017

    Haha thanks Steve! Forgot to mention I had a great 4th with the family. Back to work on the 5th though… I hope you had a good one too. 🙂

    Reply
Greg - July 6, 2017

Hi Jon,

Thanks for this. I already created my dynamic calendar some time ago, based on Ken Pulse’s method, and I’m using it in some reports, but this is the solution that I looked for, because I was struggling with the same problem of dates in the budget and real sales tables. Cool! Thanks again.

Reply
    Jon Acampora - July 6, 2017

    Hi Greg,
    Thanks for letting me know. Joining data sets with different time periods is a great use for the calendar table and Power Pivot. Much easier and more flexible than complex formulas once we get it all setup. I’m happy to hear the article helped. Thanks again!

    Reply
JoAnn Paules - July 6, 2017

I can foresee this getting A LOT of use in my work! Thank you!

Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x