A calendar table is a permanent table containing a list of dates and various components of those dates.
The calendar table is a list of continuous dates with no skipped dates. It will also typically start on the first day of the (fiscal) year and end on the last day of the (fiscal) year.
Each day can then be represented in a myriad of ways:
- Julian dates
- Day Names
- Day number within the week
- Week number
Importing the Data into the Data Model
To open the Data Model, click Data (tab) -> Data Tools (group) -> Manage Data Model. This opens the Power Pivot for Excel window.
We see in the Power Pivot window the data from the “TableSales” table loaded as a sheet in the data model.
An advantage to importing data into the Data Model is that you can apply number formatting to the data, and it will carry over into any report you place the field within.
Creating the Calendar Table
We can quickly and easily set up the foundation of the calendar table by selecting the Order date column and clicking Design (tab) -> Calendar (group) -> Date Table -> New.
We are delivered a new sheet named Calendar that has a continuous set of dates starting from 1-Jan of the oldest transaction’s year to 31-Dec of the newest transaction’s year.
The table is also populated with a variety of common ways to display and group dates.
We are not limited to only these date descriptors. We are free to create columns that describe or group the dates in any way we desire.
The Wonders of Relationships
To allow these two tables (“TablesSales” and “Calendar”) to communicate with one another, a relationship must be created that connects the tables.
This relationship will permit a date in the “TableSales” table to ‘jump’ to the “Calendar” table and figure out what the “MMM-YYYY” equivalent is. For example, “1/1/2021” can be expressed as “Jan-2021”.
There are several ways to create a relationship that connects the two tables. A common approach would be to take the following steps:
- Switch to the Diagram View by clicking Home (tab) -> View (group) -> Diagram View.
- Left-click and hold the “Date” field in the “Calendar” table and drag it atop the “Order date” field in the “TableSales” table.
A relationship has been created between the two tables using the date fields as the connector.
Hiding Fields from the Pivot Table
We need to take steps to ensure the report writer doesn’t accidentally place the “Order date” field from the “TableSales” table into the Pivot Table report. This forces the report writer to use the date entries from the “Calendar” table and not risk creating those extra groups when using the “Order date” field.
The field can be hidden by right-clicking the “Order date” field and selecting “Hide from Client Tools”.
Returning to Excel, we see the two tables from the Data Model listed in the PivotTable Fields panel.
Expanding the “TableSales” table, we see that the original “Order date” field is hidden.