Zomalex | Data and AI Training

Home | Power BI | Excel | Python | SQL | Gen AI | Contact Us | LBAG | Modern Data Model Course | info@zomalex.co.uk

Dates Table

A Dates (Calendar) table is used for many exercises and is an essential aspect of any dimensional data model. One is available in an Excel spreadsheet here. This contains an Excel table named Dates. This has a row for every date from 1960 to 2027 inclusive. Each row contains:

• the date column
• columns for the calendar year, half year quarter, month, day of the month, day of the week, week of the year
• some combination columns such as year quarter and year month
• columns for the financial year and financial quarter. (This assumes that the financial year starts in April.)

For each text column, there is a corresponding numeric key column, with a one to one relationship between a text and a numeric value. The key column numbers are arranged in chronological order. For example the Month column (values from Jan - Dec) has a corresponding Month Key column (values from 1 to 12) These key columns are used to sort the corresponding text column. Columns like Quarter where the alphabetical sort order text value e.g. Q1, Q2, Q3, Q4 is also the chronological order also have a corresponding key column. This is because Power BI will then sort these in chronological order on visuals by default, rather than by high to low values of the measure (which is the default behaviours of most charts for text columns).

How to add a Dates table to a Power BI model

We can add a Dates table in two ways:

• manually, by importing, filtering, cleaning and shaping a Dates dataset then setting properties (e.g. sort order and visibility) of several columns, or
• apply a TMDL script to create a Dates table. This is the faster method

Add a Dates table using the TMDL editor (preferred approach)

The TMDL script includes all the information about the Dates table: source dataset location, column names and types, formats, hierarchies and display folders. This makes it very quick to add a Dates table.

Here is a TMDL script to create a Dates table. If you click on the link you should see the contents in a browser tab.

1. Copy the contents from the browser tab.
2. Paste into a TMDL script window in Power BI Desktop.
3. Inspect the TMDL code.
4. Click the “Apply” button. The Dates table should now be created.
5. View the Dates table in the model view. This currently has no data. Click the “Refresh” button to import data into the Dates table.

Add a Dates table manually (alternative approach)

Use this approach if you are using Power BI online since there is currently no TMDL view online.

1. In Power BI Desktop, import the data from the Excel spreadsheet. In Power BI Online, use the URL of the Excel spreadsheet to connect to the data.
2. Check that Power BI detects all the data types of the columns of the dates table correctly. If Power BI has not automatically guessed the data type, select all columns and select ‘Detect Data Types’ to do this quickly. Change the ‘Year’ and ‘Year Month’ columns to text datatype.
3. Filter the Dates table to the years found in the Superstore dataset (2020 to 2024 inclusive).
4. Clean up the dates table. Order the text columns by their corresponding key columns then hide all the key columns.