Calendar-based time intelligence: time intelligence, tailored (Preview)

Time-based analysis is at the heart of business intelligence. Whether you’re comparing sales from two years ago, analyzing last month’s performance, or calculating totals for the current quarter, time intelligence is essential. While Power BI has long supported time-based calculations, existing functions come with limitations:

• No built-in support for week-based calculations.
• Requirement for a continuous date table.
• Limited flexibility for non-Gregorian calendars (e.g., fiscal, lunar, retail).

Introducing calendar-based time intelligence—a powerful and flexible way to work with any calendar structure in Power BI.

 

What Are Calendars?

In Power BI, calendars define which columns in a table represent specific time attributes, enabling customized time-based analysis. You can define multiple calendars on any table, giving you full control over how time is segmented and analyzed.

 

Key Benefits

• Works with any calendar: Gregorian, shifted Gregorian, retail (445, 454, 544), 13-month, lunar, and more.
• No assumptions: Power BI doesn’t impose structural rules—your calendar can be as custom as needed.
• Sparse dates supported: Continuous date tables are recommended but not required.
• Week-based calculations: New functions like TOTALWTD (week-to-date) make week-level analysis easy.
• Performance gains: Some scenarios may see improved performance compared to traditional time intelligence functions.

 

Getting Started

To enable the preview:

1. Go to Options and settings > Options > Preview features
2. Turn on Enhanced DAX Time Intelligence

Once enabled, you can define calendars using either the Calendar options UI or the TMDL view.

Defining a Calendar

 

Using the Calendar Options UI

Access the Calendar options via the Table tools tab or by right clicking a table in the Data pane.

From here, you can:

• View existing calendars
• Create new calendars
• Access the mark as date table options

A calendar consists of mappings between categories and columns. Each category requires a primary column and may include associated columns. For example, a Fiscal Calendar might define:

• Year → Fiscal Year
• Quarter → Fiscal Quarter
• Month → Fiscal Month
• Date → Fiscal Date

Complete vs. Partial Categories

• Complete categories uniquely identify a time period (e.g., “January 2024” → Month).
• Partial categories do not uniquely identify a time period (e.g., “January” → MonthOfYear).

Power BI doesn’t require specific languages or formats—what matters is the cardinality between columns.

Validation Rules

Before saving a calendar, ensure:

• Calendar name is unique
• Columns aren’t assigned to multiple categories
• Partial categories are paired with complete ones (e.g., DayOfQuarter + Quarter + Year)
• Columns are consistently categorized across calendars

Use the Validate data button to check for blank values and cardinality issues.

 

Advanced: Using TMDL View

The TMDL view offers more flexibility, including the ability to tag columns as time related. Here’s a sample snippet:

table Date

...

calendar 'Demo Calendar'

lineageTag: def

calendarColumnGroup = year

primaryColumn: Year

calendarColumnGroup = month

primaryColumn: Month

associatedColumn: DutchMonthName

associatedColumn: MonthName

column: 'Holiday Name'

column: isWorkingDay

The Demo Calendar defines the following mappings:

• The Year category has a primary column called Year
• The Month category has a primary column called Month and two associated columns (DutchMonthName and MonthName)
• The Holiday Name and isWorkingDay columns are categorized as time-related, which affects how they behave in calculations.

 

Time-related

Note that you can also identify columns as time-related, but you can only do that using the TMDL view at this time, as this option is not yet available in the calendar options. Context on any columns that are assigned to the time-related category is removed when performing calculations in all functions except DATEADD and SAMEPERIODLASTYEAR.

 

Which columns to map

We recommend you map only the columns in your table that you want to use in time intelligence calculations. Any context on columns that are part of the table on which the calendar is defined but aren’t mapped in that calendar is kept.

 

Using calendars in DAX

Once defined, calendars can be referenced in all time intelligence functions.

 

Existing function with calendar

Existing time intelligence functions have been updated to work with calendars. For example, here is how to calculate the full last year’s quantity using PARALLELPERIOD:

FullLastYearQuantity =

CALCULATE ( [Total Quantity], PARALLELPERIOD ( 'Gregorian', -1, YEAR ) )

 

New week-based calculations

Here’s how to calculate a total week-to-date value for the Total Sales measure:

Total Sales WTD = TOTALWTD ( [Total Sales], ISO-454 )

This assumes the ISO-454 calendar indeed defines complete and/or partial week categories such as Week or WeekofMonth.

Additionally, functions that expect a period value also accept WEEK:

FullLastWeekQuantity =

CALCULATE ( [Total Quantity], PARALLELPERIOD ( 'Gregorian', -1, WEEK ) )