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 timerelated 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 daterelated 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 datebased 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.
