Time Intelligence Generator

Pick a base measure and the comparisons you need: year to date, prior year, growth, rolling windows. Get the DAX as separate measures or as one calculation group, and see what each one returns before you paste it.

Calculations

Period to date

Prior period

Growth

Rolling

One measure each, for Home → New measure. Or add them all at once with the DAX query view script below.

Total Sales YTD

Total Sales YTD =
CALCULATE([Total Sales], DATESYTD('Date'[Date]))

Total Sales PY

Total Sales PY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Total Sales PYTD

Total Sales PYTD =
CALCULATE([Total Sales YTD], SAMEPERIODLASTYEAR('Date'[Date]))

Total Sales YoY %

Total Sales YoY % =
VAR CurrentValue = [Total Sales]
VAR PriorValue = [Total Sales PY]
RETURN
    IF(NOT ISBLANK(CurrentValue), DIVIDE(CurrentValue - PriorValue, PriorValue))

Total Sales Rolling 12M

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

All at once: DAX query view

Open DAX query view, paste, and run it to check the numbers. Then click the Update model link above the measures to add them to the Sales table.

DEFINE
    MEASURE 'Sales'[Total Sales YTD] = CALCULATE([Total Sales], DATESYTD('Date'[Date]))
    MEASURE 'Sales'[Total Sales PY] = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
    MEASURE 'Sales'[Total Sales PYTD] = CALCULATE([Total Sales YTD], SAMEPERIODLASTYEAR('Date'[Date]))
    MEASURE 'Sales'[Total Sales YoY %] =
        VAR CurrentValue = [Total Sales]
        VAR PriorValue = [Total Sales PY]
        RETURN
            IF(NOT ISBLANK(CurrentValue), DIVIDE(CurrentValue - PriorValue, PriorValue))
    MEASURE 'Sales'[Total Sales Rolling 12M] = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH))

-- Assumes a Year column on the date table; swap in any column you have
EVALUATE
    SUMMARIZECOLUMNS(
        'Date'[Year],
        "Total Sales", [Total Sales],
        "Total Sales YTD", [Total Sales YTD],
        "Total Sales PY", [Total Sales PY],
        "Total Sales PYTD", [Total Sales PYTD],
        "Total Sales YoY %", [Total Sales YoY %],
        "Total Sales Rolling 12M", [Total Sales Rolling 12M]
    )

Preview

What these return by month for a made-up Total Sales, with 2024–2025 behind it for the prior-year and rolling windows. At month level, month-to-date equals the month itself; it differs only on daily rows.

MonthTotal SalesTotal Sales YTDTotal Sales PYTotal Sales PYTDTotal Sales YoY %Total Sales Rolling 12M
Jan 20269,7979,7979,2109,2106.4%133,964
Feb 20269,04818,8458,50817,7186.3%134,504
Mar 202611,24030,08510,56828,2866.4%135,176
Apr 202611,82341,90810,58438,87011.7%136,415
May 202611,95053,85811,23650,1066.4%137,129
Jun 202611,94865,80611,23261,3386.4%137,845
Jul 202610,67276,47810,03571,3736.3%138,482
Aug 202611,12287,60010,45781,8306.4%139,147
Sep 202612,667100,26711,34093,17011.7%140,474
Oct 202612,888113,15512,118105,2886.4%141,244
Nov 202614,099127,25413,254118,5426.4%142,089
Dec 202615,776143,03014,835133,3776.3%143,030

Before these will work

  1. Date has to be marked as a date table, with one row for every day and no gaps. If you don't have one yet, the Date Table Generator builds it.
  2. Visuals must slice by columns from that date table, not by a date column on the fact table, or the time shifts have nothing to move.
  3. Format Total Sales YoY % as a percentage.

Free, with no sign-in and no request limit. Everything runs in your browser, and nothing you type is sent anywhere.

Measures or a calculation group? Separate measures are simplest when you only need these comparisons for one or two base measures. Once you need YTD and prior year for Sales, Cost, Margin, and Units, a calculation group writes each calculation once and applies it to all of them.

Want to understand what these functions actually do? See Time Intelligence and the DAX Time Intelligence Cheat Sheet.