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:
- Go to Options and settings > Options > Preview features
- 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.
To learn more in our documentation.
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 ) )
Try it today!
Calendar-based time intelligence gives you unmatched flexibility and is the only way to perform week-based calculations using built-in functions. Enable the preview today and explore the possibilities! Learn more in our documentation and let us know what you think.