Time Intelligence in Power BI, With DAX Examples

Time intelligence in Power BI is a set of DAX functions, such as TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD, that calculate a value over a specific time period like year-to-date or the same month one year earlier. These functions return reliable results only against a complete date table, with one row per day, related to your fact tables and marked as the date table. This guide covers the functions you need most, the date table they depend on, and how CALCULATE makes them work.

What time intelligence does

Time intelligence functions calculate a measure over a time-based range instead of the range a visual happens to be filtered to. Common ranges include:

• Month-to-date (MTD)

• Quarter-to-date (QTD)

• Year-to-date (YTD)

• Previous month or previous year

• Rolling totals, such as the last 30 days or last 12 months

• Year-over-year (YoY) growth

Each of these adjusts automatically as a reader changes slicers and filters, because the functions work by changing filter context, not by hardcoding a date range.

The requirement: a dedicated date table

Time intelligence functions require a continuous date column with no missing days. Your model needs:

• A calendar table with one row per date, no gaps

• A relationship between the calendar table and your fact table

• A date column marked as Date Table under Table tools > Mark as date table, with the date column selected

A common mistake is filtering visuals directly on a fact table's date column, such as Sales[OrderDate], instead of the calendar table. That works for simple filtering, but it breaks time intelligence once dates are missing or a table holds only transaction dates, not every calendar day. Build the calendar table with DAX and give it useful columns:

DateTable =
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

Our guide to creating date tables in Power BI covers the full setup, including fiscal calendars.

Core time intelligence functions

For these examples, assume a base measure:

Total Sales = SUM(Sales[SalesAmount])

Time intelligence works on measures, not calculated columns, because it needs filter context to shift.

Year-to-date (YTD)

Sales YTD = TOTALYTD([Total Sales], DateTable[Date])

TOTALYTD takes the expression to total, the date column, an optional filter, and an optional year-end date for a fiscal calendar. For a fiscal year ending on the last day of the sixth month, pass a filter such as ALL(DateTable) as the third argument and the year-end date as the fourth, the form Microsoft documents:

Sales Fiscal YTD = TOTALYTD([Total Sales], DateTable[Date], ALL(DateTable), "6/30")

The year-end string is read in the locale of the file, so "6/30" suits a US-locale file. If you write a year in it, Power BI ignores the year.

Our guide to YTD calculations in Power BI covers YTD in more depth, including comparisons to prior YTD.

Month-to-date and quarter-to-date

Sales MTD = TOTALMTD([Total Sales], DateTable[Date])
Sales QTD = TOTALQTD([Total Sales], DateTable[Date])

Previous year and year-over-year growth

Sales Previous Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTable[Date]))

Sales YoY % = DIVIDE([Total Sales] - [Sales Previous Year], [Sales Previous Year])

SAMEPERIODLASTYEAR returns the same set of dates from one year earlier, shifted from whatever the current filter context is. DIVIDE returns blank instead of an error when the previous year has no sales. Set the measure's format to Percentage to display Sales YoY % correctly.

Previous month

Sales Previous Month = CALCULATE([Total Sales], PREVIOUSMONTH(DateTable[Date]))

Rolling totals

Sales Rolling 12M =
CALCULATE(
    [Total Sales],
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH)
)

Sales Last 30 Days =
CALCULATE(
    [Total Sales],
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -30, DAY)
)

DATESINPERIOD takes the date column, a start date, a number of periods, and the period type. A negative number moves backward from the start date, so -12, MONTH returns the 12 months ending on the last date in the current filter context.

Same month one year earlier

Sales Same Month One Year Ago = CALCULATE([Total Sales], DATEADD(DateTable[Date], -1, YEAR))

DATEADD shifts the current date range by a number of intervals, here one year back. It is more flexible than SAMEPERIODLASTYEAR because the interval and unit are both arguments, so the same pattern shifts by a month or a quarter instead.

Why CALCULATE is the function behind all of this

Every time intelligence example above wraps CALCULATE around a base measure. CALCULATE changes the filter context a measure evaluates in. SAMEPERIODLASTYEAR(DateTable[Date]), PREVIOUSMONTH(DateTable[Date]), and DATEADD(DateTable[Date], -1, YEAR) are all filter arguments that tell CALCULATE to replace the current date filter with a shifted one. That is the whole mechanism: time intelligence functions build a new set of dates, and CALCULATE swaps that set in for the current one.

Custom and fiscal calendars

Not every business runs on a calendar-year fiscal period. For fiscal years that start mid-year, retail 4-4-5 calendars, or other custom periods, extend the date table with columns like Fiscal Year, Fiscal Month, and Week Start Date. Then use those columns inside CALCULATE and FILTER instead of the built-in functions. The built-in functions assume a standard calendar, unless you pass a fiscal year-end date as shown above with TOTALYTD.

Checking your results

If a YTD or rolling measure returns blank or an unexpected total, check three things in order:

1. The date table has no gaps and is marked as the date table.

2. The relationship between the date table and the fact table is active and one-to-many.

3. No other filter (a slicer, a page filter, a disconnected table) is restricting the date range.

REMOVEFILTERS() inside CALCULATE can rule out a stray filter while you debug.

FAQs

What is time intelligence in Power BI?

A group of DAX functions, such as TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD, that calculate a measure over a time-based range like year-to-date, a rolling 12 months, or the same period one year earlier.

Why is a date table required for time intelligence?

Time intelligence functions need a continuous, gap-free date range to shift over. Filtering on a fact table's own date column instead of a marked date table causes missing or duplicated periods, which produces wrong or blank results.

Sources

• Time intelligence functions (DAX) - Microsoft Learn

• Mark a table as a date table - Microsoft Learn