Power BI Lesson 37 – Time Intelligence | Dataplexa

DAX · Lesson 37

Time Intelligence

Time intelligence is the ability to compare values across time periods — this year vs last year, this month vs the prior month, year-to-date vs the same period last year. DAX provides a dedicated set of functions for these calculations, but they all depend on one prerequisite: a properly built Calendar table connected to every fact table through a Date-type relationship. Get the Calendar table right and the time intelligence functions work simply. Skip it, and nothing works.

The Prerequisite — Calendar Table Setup

Before any time intelligence function will work, three things must be true. All three are required — missing any one causes every time intelligence measure to return BLANK or wrong values without any error message.

Calendar table has a continuous, unbroken date sequence

One row per date, no gaps, covering the full date range of your fact tables. A missing date causes time intelligence calculations to produce wrong results for any period containing that gap.

Calendar[Date] column is type Date — not DateTime, not Text

If the fact table's date column is DateTime and the Calendar is Date, the relationship will not match any rows. Change both to Date type in Power Query before building the relationship.

Relationship: Orders[OrderDate] → Calendar[Date] (Many-to-One)

The fact table is on the "many" side (multiple orders per date). Calendar is on the "one" side (one row per date). All time intelligence functions navigate this relationship to find matching date ranges.

The Working Dataset

Orders — 12 months of data used for all examples

Month	Revenue	Prior Year Same Month	Notes
Jan 2023	4,200	—	Start of dataset
Feb 2023	3,800	—
Mar 2023	5,100	—
Q1 2023 Total	13,100	—
Apr–Dec 2023	38,900	—	Full year 2023 = 52,000
Jan 2024	5,100	4,200	+21% YoY
Feb 2024	4,600	3,800	+21% YoY
Mar 2024	6,200	5,100	+22% YoY
Q1 2024 Total	15,900	13,100	+21.4% YoY

Year-to-Date — TOTALYTD and DATESYTD

Year-to-date accumulates revenue from January 1 of the current year through the last date in the current filter context. Use TOTALYTD for a single-expression form, or CALCULATE with DATESYTD when you need to combine with other CALCULATE modifiers.

// TOTALYTD — simplest YTD form
Revenue YTD =
    TOTALYTD(
        SUM(Orders[Revenue]),
        Calendar[Date]
    )

// CALCULATE + DATESYTD — same result, more composable
Revenue YTD =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATESYTD(Calendar[Date])
    )

// Fiscal year YTD — use the year-end-date argument for non-calendar years
// Fiscal year ending March 31 (April start):
Revenue FY YTD =
    TOTALYTD(
        SUM(Orders[Revenue]),
        Calendar[Date],
        "3/31"          -- year end date; YTD resets April 1
    )

Revenue YTD — monthly view, 2024

Month	Monthly Revenue	Revenue YTD
Jan 2024	5,100	5,100
Feb 2024	4,600	9,700
Mar 2024	6,200	15,900
Apr 2024	4,800	20,700
Dec 2024	6,100	61,400

YTD accumulates from Jan 1 through each month · resets to zero on Jan 1 of the next year · Dec YTD = full year total

Same Period Last Year — SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR returns the dates in the current filter context shifted back by exactly one year. Use it inside CALCULATE to evaluate any measure over the prior year's equivalent period — whether the context is a month, a quarter, or a year.

// Prior Year Revenue — same period one year ago
Revenue PY =
    CALCULATE(
        SUM(Orders[Revenue]),
        SAMEPERIODLASTYEAR(Calendar[Date])
    )

// Year-over-Year growth amount
Revenue YoY Change =
    SUM(Orders[Revenue])
    - CALCULATE(SUM(Orders[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date]))

// Year-over-Year growth percentage — with VAR for readability
Revenue YoY % =
VAR CurrentRev = SUM(Orders[Revenue])
VAR PriorRev   =
    CALCULATE(
        SUM(Orders[Revenue]),
        SAMEPERIODLASTYEAR(Calendar[Date])
    )
RETURN
    DIVIDE(CurrentRev - PriorRev, PriorRev)

Revenue PY, YoY Change and YoY % — monthly view Q1 2024

Month	Revenue	Revenue PY	YoY Change	YoY %
Jan 2024	5,100	4,200	+900	+21.4%
Feb 2024	4,600	3,800	+800	+21.1%
Mar 2024	6,200	5,100	+1,100	+21.6%
Q1 2024 Total	15,900	13,100	+2,800	+21.4%

Total row correctly aggregates the quarter — SAMEPERIODLASTYEAR shifts Q1 2024 context back to Q1 2023 for the denominator

Prior Year YTD Comparison

Combining DATESYTD with SAMEPERIODLASTYEAR gives a YTD figure for the same point in the prior year — the most common executive dashboard comparison.

// Revenue YTD for the prior year — at the same point in the year
Revenue PY YTD =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
    )

// YTD vs Prior YTD growth %
Revenue YTD vs PY YTD % =
VAR CurrentYTD  = CALCULATE(SUM(Orders[Revenue]), DATESYTD(Calendar[Date]))
VAR PriorYTD    =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
    )
RETURN
    DIVIDE(CurrentYTD - PriorYTD, PriorYTD)

YTD vs Prior Year YTD — at each month end in 2024

Month	Revenue YTD (2024)	Revenue PY YTD (2023)	YTD Growth %
Jan 2024	5,100	4,200	+21.4%
Feb 2024	9,700	8,000	+21.3%
Mar 2024	15,900	13,100	+21.4%
Jun 2024	31,200	25,800	+20.9%

Both YTD measures accumulate from Jan 1 of their respective years · comparing them shows whether this year is running ahead or behind at the same point last year

DATEADD — Flexible Period Shifting

DATEADD shifts the current date context by any number of days, months, quarters, or years in either direction. It is more flexible than SAMEPERIODLASTYEAR because you control both the interval type and the direction.

// DATEADD(dates, number_of_intervals, interval)
// interval: DAY, MONTH, QUARTER, YEAR
// Positive = forward in time, Negative = backward

// Prior month revenue
Revenue Prior Month =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATEADD(Calendar[Date], -1, MONTH)
    )

// Prior quarter revenue
Revenue Prior Quarter =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATEADD(Calendar[Date], -1, QUARTER)
    )

// Two years ago revenue
Revenue 2Y Ago =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATEADD(Calendar[Date], -2, YEAR)
    )

// Month-over-Month growth %
Revenue MoM % =
VAR CurrentRev = SUM(Orders[Revenue])
VAR PriorRev   =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATEADD(Calendar[Date], -1, MONTH)
    )
RETURN
    DIVIDE(CurrentRev - PriorRev, PriorRev)

DATEADD — Prior Month and MoM % — Jan through Apr 2024

Month	Revenue	Prior Month	MoM %
Jan 2024	5,100	4,900	+4.1%
Feb 2024	4,600	5,100	-9.8%
Mar 2024	6,200	4,600	+34.8%
Apr 2024	4,800	6,200	-22.6%

DATEADD(-1, MONTH) shifts the filter context back one calendar month · Dec 2023 (4,900) becomes Jan 2024's prior month

Running Total

A running total accumulates all revenue from the beginning of time (or the beginning of the year) through the current date. It is different from YTD because it does not reset at the year boundary — it keeps growing indefinitely.

// All-time running total — accumulates from the beginning of the dataset
Revenue Running Total =
    CALCULATE(
        SUM(Orders[Revenue]),
        FILTER(
            ALL(Calendar[Date]),
            Calendar[Date] <= MAX(Calendar[Date])
        )
    )
// MAX(Calendar[Date]) = the last date in the current filter context
// FILTER on ALL(Calendar[Date]) = all dates up to and including that date
// CALCULATE evaluates SUM for that expanded date range

// Running total within the current year only
Revenue Running Total YTD =
    CALCULATE(
        SUM(Orders[Revenue]),
        FILTER(
            ALL(Calendar),
            Calendar[Year] = MAX(Calendar[Year])
            && Calendar[Date] <= MAX(Calendar[Date])
        )
    )
// Same as DATESYTD but written explicitly — shows the logic clearly

Revenue Running Total — cumulative from Jan 2023 onwards

Month	Monthly Revenue	Running Total (all-time)
Jan 2023	4,200	4,200
Feb 2023	3,800	8,000
Dec 2023	5,400	52,000
Jan 2024	5,100	57,100
Feb 2024	4,600	61,700
Mar 2024	6,200	67,900

Running total does NOT reset at year boundary (unlike YTD) · Jan 2024 running total = 52,000 + 5,100 = 57,100 · use for cumulative sales, lifetime customer value, or total units shipped

Quarter-to-Date and Month-to-Date

// Month-to-Date
Revenue MTD =
    TOTALMTD(SUM(Orders[Revenue]), Calendar[Date])
// Equivalent: CALCULATE(SUM(Orders[Revenue]), DATESMTD(Calendar[Date]))

// Quarter-to-Date
Revenue QTD =
    TOTALQTD(SUM(Orders[Revenue]), Calendar[Date])
// Equivalent: CALCULATE(SUM(Orders[Revenue]), DATESQTD(Calendar[Date]))

// Prior month MTD — MTD at the same point in the prior month
Revenue Prior MTD =
    CALCULATE(
        TOTALMTD(SUM(Orders[Revenue]), Calendar[Date]),
        DATEADD(Calendar[Date], -1, MONTH)
    )

MTD and QTD — daily view, first week of Feb 2024

Date	Daily Revenue	Revenue MTD	Revenue QTD
Feb 1	180	180	5,280
Feb 2	210	390	5,490
Feb 3	165	555	5,655

MTD resets at the start of each month · QTD includes Jan 2024 (5,100) plus Feb days so far · QTD resets at the start of each quarter

Time Intelligence Function Reference

Function	Returns	Common use
TOTALYTD	Year-to-date from Jan 1 to current date	Running YTD total on a chart or card
TOTALMTD	Month-to-date from 1st of month	Current month running total for ops dashboards
TOTALQTD	Quarter-to-date from 1st of quarter	Quarterly progress tracking
DATESYTD	Table of dates from Jan 1 to current — use inside CALCULATE	When you need to combine YTD with other CALCULATE modifiers
SAMEPERIODLASTYEAR	Dates shifted back exactly one year	YoY comparison — prior year same month / quarter / year
DATEADD	Dates shifted by N days/months/quarters/years	Prior month/quarter, rolling N-month windows, forecasting
PARALLELPERIOD	Complete parallel period (full month/quarter/year) N periods ago	Prior full quarter when current context is mid-quarter
PREVIOUSMONTH / YEAR	All dates in the previous complete month or year	Prior full period comparison when context is always complete
DATESMTD / QTD / YTD	Table of dates MTD / QTD / YTD — use inside CALCULATE	Composable versions of TOTALMTD / TOTALQTD / TOTALYTD

Common Time Intelligence Gotchas

No Calendar
table

All time intelligence functions return BLANK with no error. The function silently fails. Always verify the Calendar table exists, has a Date-type column, and is connected via a relationship before debugging time intelligence measures.

DateTime vs
Date mismatch

If Orders[OrderDate] is DateTime and Calendar[Date] is Date, no rows match in the relationship. Time intelligence returns BLANK for all dates. Fix by changing OrderDate to type Date in Power Query before the relationship is created.

Calendar
gaps

A Calendar table missing dates causes DATESYTD to skip those dates — the YTD measure jumps incorrectly. Always generate the Calendar from List.Dates in Power Query (Lesson 28) to guarantee no gaps.

SPLY at year
boundary

If the Calendar table does not extend back far enough, SAMEPERIODLASTYEAR returns BLANK for any period whose prior year falls outside the Calendar range. Make the Calendar dynamic (Lesson 28) and always extend it at least one year before the earliest date in the fact table.

Teacher's Note: When a time intelligence measure returns BLANK, the first thing to check is not the DAX formula — it is the Calendar table setup. Ninety percent of time intelligence failures come from one of three things: the Calendar is missing, the date column type is wrong, or the Calendar does not cover the full date range of the fact table. Open Model View, confirm the relationship exists and points the right way (Orders → Calendar, not the reverse), click on Calendar[Date] and verify the column type is Date in the Data pane, then check the min and max dates in the Calendar cover all dates in Orders. Only after confirming all three should you look at the measure formula itself.

Practice

Practice 1 of 3

To calculate revenue accumulated from January 1 of the current year through the latest date in the current filter context, you use ___(SUM(Orders[Revenue]), Calendar[Date]) — the simplest single-expression YTD function.

Practice 2 of 3

To shift the current date context back by exactly three months — for example, to show Q1 revenue when Q2 is currently selected — you use CALCULATE(SUM([Revenue]), ___(Calendar[Date], -3, MONTH)).

Practice 3 of 3

If all time intelligence measures return BLANK with no error message, the most likely cause is that the ___ table is either missing, has a DateTime rather than Date column, or does not cover the full date range of the fact table.

Lesson Quiz

Quiz 1 of 3

A Revenue YTD measure works correctly for all months except January, which always shows the same value as the monthly revenue rather than accumulating. What is the most likely cause?

TOTALYTD resets on December 31 by default and January is a known edge case — use DATESYTD with the fiscal year end argument set to "12/31" to override the reset behaviour for calendar years This is correct behaviour — in January there is only one month in the YTD range (January itself), so the YTD value equals the monthly value. February YTD will include January + February, and so on. The measure is working correctly; January YTD = January revenue is expected. The Calendar table is missing a relationship for January — TOTALYTD only accumulates for months that have at least two connected rows in the Calendar table, so single-month contexts fall back to the monthly value The Orders table has no data before January, causing DATESYTD to produce an empty date range for January — wrap the measure in IFERROR to return the monthly value as the fallback for the first month

Quiz 2 of 3

Your SAMEPERIODLASTYEAR measure returns BLANK for January and February 2024 but works correctly for all other months. Your Calendar table covers 2023-01-01 to 2024-12-31. What is the problem?

SAMEPERIODLASTYEAR cannot look back to a prior year if the current year is the first year in the Calendar table — you must add a dummy row for December 31 of the year before the Calendar start date to enable prior-year lookups January and February are the shortest months and SAMEPERIODLASTYEAR requires at least 28 days of matching data in the prior year period — the Calendar table must include at least 28 prior-year days before the current context for the function to return a result The Calendar covers back to 2023-01-01, so prior-year data for Jan and Feb 2024 (which maps to Jan and Feb 2023) should exist. The more likely cause is that the Orders table has no data for Jan and Feb 2023 — SAMEPERIODLASTYEAR shifts the dates correctly but there are no orders in those months, so SUM returns BLANK. Check if your data starts from March 2023. SAMEPERIODLASTYEAR has a known bug with Q1 months — use DATEADD(Calendar[Date], -12, MONTH) instead, which produces identical results and does not have the January–February limitation

Quiz 3 of 3

An executive wants a report showing both the current year YTD revenue and the prior year YTD revenue at the same point — so in April, both measures show Jan–April figures for their respective years. Which pair of measures is correct?

Current: TOTALYTD(SUM([Revenue]), Calendar[Date]) and Prior: CALCULATE(SUM([Revenue]), DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))) — the prior measure shifts dates back one year and then takes the YTD range within that shifted period, giving Jan–Apr of the prior year when the current context is Apr. Current: TOTALYTD(SUM([Revenue]), Calendar[Date]) and Prior: CALCULATE(SUM([Revenue]), SAMEPERIODLASTYEAR(Calendar[Date])) — SAMEPERIODLASTYEAR already returns YTD data for the prior year automatically when the context is a YTD range Current: TOTALYTD(SUM([Revenue]), Calendar[Date]) and Prior: CALCULATE(TOTALYTD(SUM([Revenue]), Calendar[Date]), DATEADD(Calendar[Date], -1, YEAR)) Both measures should use TOTALYTD — the prior year measure just needs a filter: TOTALYTD(SUM([Revenue]), Calendar[Date], Calendar[Year] = YEAR(TODAY())-1)

Next up — Lesson 38 covers Iterator Functions, explaining how SUMX, AVERAGEX, COUNTX, MAXX, MINX, and RANKX evaluate row-by-row expressions before aggregating, when they produce different results from their simpler counterparts, and the patterns where they are the only correct solution.

← Previous Course Index Next →

Power BI Course