Prep 2: Date Table

Why do you need to mark your date table as a date table in Power BI ? And why do you even need a Date table in Power BI ? A recognised Date Table in Power BI enables special functionality and optimisations related to date-based calculations and time intelligence. At a low level, here’s what happens when you mark a table as a date table in Power BI:

Date Detection:

Power BI analyses the selected table’s columns to determine if it contains date or time- related values. It looks for columns with data types such as Date, DateTime, or Time.

Calendar Table Creation:

If the selected table is identified as a date table, Power BI automatically creates a hidden calendar table in the background. This calendar table serves as the foundation for date- related calculations and functions.

Calendar Table Structure:

The created calendar table typically includes columns such as Date, Year, Month, Day, Quarter, Week, etc., depending on the granularity of your original date column. These columns provide the necessary time components for performing time intelligence calculations.

Relationships Establishment:

Power BI establishes relationships between the original table (containing the date column) and the newly created calendar table. This relationship is typically set based on the date column in both tables.

Special Time Intelligence Functions:

When a table is marked as a date table, Power BI recognises it as a source of time intelligence. As a result, it activates and enhances certain DAX (Data Analysis Expressions) functions specifically designed for time calculations. These functions include DATEADD, SAMEPERIODLASTYEAR, TOTALYTD, etc.

Time Intelligence Optimisations:

Marking a table as a date table enables Power BI to apply various performance optimisations for time-based calculations. This optimisation involves aggregating data at different time granularities, pre-calculating common time-based calculations, and optimising queries related to time intelligence.

Automatic Time Hierarchy:

Power BI automatically creates a time hierarchy based on the columns in the calendar table. This hierarchy enables users to easily navigate and drill down into different time periods (year, quarter, month, etc.) in visuals and reports.

By marking a table as a date table in Power BI, you gain access to a range of built-in time intelligence features and optimisations. These functionalities simplify the creation of date- based calculations, enable efficient time-based analysis, and enhance the overall performance of your reports and visualisations.

There are however other ways to create a Date table and create all of the time intelligence formulas you will ever need using a third-party tool called Bravo for Power BI. We will demo Bravo on Day 2 but will push on with the foundational date table technique.