One-Click Data Model Date Table

Using the Data Model (or Power Pivot) helps us build some amazing PivotTables. A date table (or calendar table) is integral to most data models as they allow us to group reports by various date periods and use a wide variety of time intelligence functions. Although there are many ways to build such a date table, this post demonstrates how to build one by clicking a single command icon.

Video

I’ve created a short video along with a full narrative to walk through the steps.

Short Version

The short answer is to use the Date Table > New command on the Power Pivot Design ribbon tab.

Note: the Date Table > New command is available in the current release of Excel, and depending on the version of Excel you are using, you may not have this command available.

Now, let’s go through the long answer and the detailed steps below.

Detailed Steps

Let’s pretend we had a bunch of transactions in a table, and we wanted to build a PivotTable report that showed the totals by the day of the week. We’ll accomplish this by completing the following steps together:

• Insert a PivotTable
• Create Date Table
• Define Relationship
• Group by Day of Week

Insert a PivotTable

First up, we need to load the transactions into the data model and get a basic PivotTable set up. The first several rows of our Transactions table is shown below.

To insert a PivotTable AND load the transactions table into the data model in a single step, select any cell in the table and select:

• Insert > PivotTable

Excel displays the Create PivotTable dialog, as shown below.

Be sure to check the “Add this data to the Data Model” checkbox and click OK.

If we wanted to do something simple, like view the monthly totals, we could simply drag the Date field into the Rows area, and the Amount field into the Values area. The resulting PivotTable is shown below.

But, when our report requirements are more complex, for example grouping by day of week or using time intelligence functions, we’ll need to add a date/calendar table to the data model.

To add a date table, we’ll need to open the Power Pivot window. One way to do this is by clicking the Data >Manage Data Model command as shown below.

Note: depending on your screen resolution, you may not see the label and instead see the icon only

If you haven’t previously activated the Power Pivot add-in, you will probably see this dialog:

If so, just click Enable to proceed.

Note: you can also activate the add-in manually by going to Excel Options > Manage COM Add-ins > Microsoft Power Pivot for Excel.

Now that the Power Pivot window is open, we insert a date table by clicking the following command icon:

• Design > Date Table > New

Excel inserts a new date table, the first few rows of which are shown below.

Note: the Date Table > New command is available in the current release of Excel, and depending on the version of Excel you are using, you may not have this command available.

The data model now has both the Transactions table and the new date table named Calendar.

It is important to note that this basic date table is just the beginning. If needed, we can add a variety of calculated columns to the date table, or even change the date range by using:

• Date Table > Update Range

Before we leave the Power Pivot window, we need to define the relationship.

Define Relationship

We need to tell Excel that our two tables are related to each other by the date column. There are several ways to build this relationship. One way is to toggle into diagram view by clicking Home > Diagram View. Once here, we simply click-and-drag the Date field from the Transactions table to the Date field in the Calendar table. The relationship is created, as shown below.

We are in good shape at this point, and can now update the PivotTable. To return to Excel, we can just click File > Close (or the little Excel icon).

Group by Day of Week

We are now free to use any of the fields in the Calendar table with our PivotTable. For example, if we wanted to group by day of the week instead of by months, we can simply remove all of the current Row fields, and then insert the Day of Week field from the Calendar table. The updated report is shown below.

Note: if you don’t see the Calendar table listed in the PivotTable Fields panel, click the All label to display all tables in the data model.

Yay … we built our desired report!

Thanks Microsoft for this date table command gift 🙂

Please feel free to download the sample file below in case you’d like to check it out. And, if you have any other fun Power Pivot tips, please share by posting a comment below.

Sample file

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

1. Ивелина Димова on February 7, 2019 at 11:02 am

Hello, I’m trying to change the range of my date table adding the new year 2019 but it doesn’t work. the option is active but when I choose the period to extendseems that it is doing something for a second but then the new period doesn’t apear. Can you help me please?

2. Duncan Williamson on August 26, 2019 at 3:45 am

Ivelina,
I am not sure if this question is still live but I have just seen it.
You can change the range of dates in your Date table in this way.
In Power Pivot … Design tab, Calendars … Data Table drop down arrow … Update range
This presents you with a small dialogue box in which you can enter you starting and ending dates and when you click OK you table should update.

Duncan

• Ollie on January 21, 2022 at 9:23 am

Hello,
I have followed the some steps as Ivelina, but the “Update Range” option is greyed out for me. Is there a particular reason for this or a solution to fix this problem?
Thanks,
Ollie

3. karo on December 2, 2019 at 2:19 pm

I am using Office 365 and it has the feature for creating date tables, however, the “New” option is greyed out, hence I am unable to create a date table

4. Artieboy on July 17, 2020 at 3:00 pm

Does the date table update automatically to match the dates in the transaction table when the transaction table iitself is updated?