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

16 comments

Your email address will not be published. Required fields are marked *

  • What’s up,I check your blogs named “The Calendar Table (Date Dimension) Explained for Power Pivot & Power BI – Excel Campus” regularly.Your writing style is witty, keep up the good work! And you can look our website about powerful love spells.

    • It’s laborious to seek out educated individuals on this subject, however you sound like you know what you’re talking about! Thanks

  • Hello,

    I capture data files monthly of our employee population. Same data fields but just time frame. I am trying to figure out if I can join all of these in powerpivot and do I need the calendar table to do that?

  • 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?

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

  • 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

  • Dear Team,

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

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

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

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly