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.

Add Date Table

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

• DateTable After (Complete)
• DateTable Before (Data Only)