Power BI Advanced DAX Time Intelligence Functions

Time intelligence functions are a category of DAX functions specifically designed for date-based calculations. They allow you to calculate metrics like year-to-date totals, month-over-month growth, same-period-last-year comparisons, and rolling averages — calculations that are essential in virtually every business report but are difficult to build without dedicated time functions.

The Requirement — A Proper Date Table

Time intelligence functions work correctly only when your data model contains a dedicated Date table that meets specific requirements:

  • The table must have one row for every single calendar date in your analysis period — no gaps allowed.
  • One column must be set as the date column (Date type).
  • The table must be marked as a Date table in Power BI (right-click the table in Model View and select Mark as Date Table).
  • The date column in this table must be connected to the date column in your fact table through a relationship.

Without a proper Date table, functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD produce incorrect results or errors.

Year-to-Date Calculations

Year-to-date (YTD) shows the cumulative total from the first day of the current year up to whatever date is currently selected in the report. It answers: "How much have we sold so far this year, from January 1 to today?"

TOTALYTD

The simplest way to calculate YTD:

Revenue YTD = TOTALYTD(SUM(Sales[Revenue]), Dates[Date])

Dates[Date] is the date column in your Date table. This function automatically resets at the start of each new year — January 1 — so the cumulative total starts fresh each year.

If your financial year does not start in January, add the year-end date as a third argument:

Revenue YTD (FY) = TOTALYTD(SUM(Sales[Revenue]), Dates[Date], "31-03")

This resets the YTD on April 1 instead of January 1 — matching an Indian financial year that runs April to March.

Quarter-to-Date and Month-to-Date

TOTALQTD and TOTALMTD follow the same pattern for quarter and month cumulative totals:

Revenue QTD = TOTALQTD(SUM(Sales[Revenue]), Dates[Date])

Revenue MTD = TOTALMTD(SUM(Sales[Revenue]), Dates[Date])

Same Period Last Year

This calculation compares a period's performance to the exact same period one year earlier. A January 2024 total compares to January 2023. A Q3 2024 total compares to Q3 2023.

SAMEPERIODLASTYEAR

Revenue SPLY = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date]))

SAMEPERIODLASTYEAR shifts the date context back exactly one year. Combined with your current period measure, you can calculate year-over-year growth:

YoY Growth % = DIVIDE([Total Revenue] - [Revenue SPLY], [Revenue SPLY], 0)

When this measure shows 0.15, it means revenue grew 15% compared to the same period last year. When it shows -0.08, revenue declined 8%.

DATEADD — Flexible Time Shifts

DATEADD is more flexible than SAMEPERIODLASTYEAR. It shifts dates by any number of intervals — days, months, quarters, or years — in either direction (backward or forward).

Revenue from two months ago:

Revenue 2 Months Ago = CALCULATE(SUM(Sales[Revenue]), DATEADD(Dates[Date], -2, MONTH))

Revenue from the same quarter last year:

Revenue Last Year Q = CALCULATE(SUM(Sales[Revenue]), DATEADD(Dates[Date], -1, YEAR))

The negative number moves backward in time. A positive number moves forward — useful when forecasting or comparing actual to a future planned period.

DATESBETWEEN and DATESINPERIOD

These functions return a table of dates within a specified range. You use them inside CALCULATE to restrict calculations to a specific time window.

DATESBETWEEN

Returns dates between two specific dates:

Revenue Q1 2024 = CALCULATE(SUM(Sales[Revenue]), DATESBETWEEN(Dates[Date], DATE(2024,1,1), DATE(2024,3,31)))

This always returns Q1 2024 revenue regardless of what time period is selected in the report — it is hardcoded to that specific date range.

DATESINPERIOD

Returns dates in a rolling period relative to a specified date:

Rolling 90 Day Revenue = CALCULATE(SUM(Sales[Revenue]), DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]), -90, DAY))

LASTDATE(Dates[Date]) picks up the last date currently in context. Going back 90 days from there creates a rolling 90-day window. As the date selection in the report changes, this window moves with it.

Running Totals with DATESYTD

DATESYTD returns a table of all dates from the start of the year to the current date. Use it inside CALCULATE as a filter argument for more complex YTD scenarios than TOTALYTD can handle:

Custom YTD = CALCULATE([Total Revenue], DATESYTD(Dates[Date], "31-03"))

This is functionally equivalent to TOTALYTD but gives you the flexibility to use any measure (not just aggregations) inside the calculation — useful for complex YTD calculations involving multiple intermediate measures.

PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR

These functions return the complete set of dates from the previous month, quarter, or year respectively:

Revenue Previous Month = CALCULATE(SUM(Sales[Revenue]), PREVIOUSMONTH(Dates[Date]))

If the current month selected is March 2024, this returns February 2024's complete revenue — not just February up to the same day number as March.

Use these alongside current period measures to build MoM (Month-over-Month) comparison tables and KPI cards that show "vs previous period" indicators.

Practical Example — Monthly Revenue Trend with YoY Comparison

A complete time intelligence dashboard for a sales team typically includes these measures working together:

  • Total Revenue: SUM(Sales[Revenue]) — current period total
  • Revenue YTD: TOTALYTD([Total Revenue], Dates[Date]) — cumulative year progress
  • Revenue SPLY: CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date])) — same period last year
  • YoY Growth %: DIVIDE([Total Revenue] - [Revenue SPLY], [Revenue SPLY], 0) — growth rate
  • Revenue MoM Change: [Total Revenue] - CALCULATE([Total Revenue], PREVIOUSMONTH(Dates[Date])) — month change

Place these measures in a card row at the top of your report page. Add a line chart below showing Revenue and Revenue SPLY on the same axis across months — viewers instantly see current performance vs the same period last year with the gap between lines showing growth or decline.

Key Points

  • Time intelligence functions require a dedicated Date table with no date gaps, marked as a Date table in Power BI.
  • TOTALYTD, TOTALQTD, and TOTALMTD calculate cumulative period totals with a fiscal year-end option.
  • SAMEPERIODLASTYEAR shifts context back exactly one year for year-over-year comparisons.
  • DATEADD provides flexible time shifts by any interval — useful for custom prior-period comparisons.
  • DATESINPERIOD enables rolling window calculations (last 30 days, last 90 days) that move with report filters.
Previous lessons
Back to courses

Leave a Comment