Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 56 ยท DAX

Time Intelligence: YTD, Prior Year and Moving Averages

Write DAX time intelligence measures: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESINPERIOD, growth percent and rolling averages, with pandas stand-ins.

"How are we doing compared to last year?" is the most common question a business report has to answer. Year to date, the same month last year, growth percent and a smoothed three-month trend are all time intelligence: calculations that move or widen the date filter. DAX has a family of functions for them, and they are all built on CALCULATE from the previous lesson.

Time intelligence only works on a proper date table: a table with one row for every day, no gaps, marked as the date table. If you have not done that yet, read Date Tables and Time Intelligence Setup first. Every example below assumes a 'Date' table related to Sales[OrderDate].

DAX runs inside Power BI, not on this site, so the DAX is read-only. The runnable examples are pandas stand-ins that compute the same numbers on a small monthly series. They show what each measure calculates; the way DAX gets there (by changing the filter context) is different, and the text says so where it matters.

The base measure

All time measures wrap one simple base measure, written once:

DAX
Total Sales = SUM(Sales[Amount])

Keep the base measure separate. Then each time measure only has to say which dates to use.

Four common time questions

Year to date January until now.
Prior year The same period, a year ago.
Growth % This year against last.
Rolling average Smooth out the noise.

Each one changes the date filter, nothing else.

1 / 2

Year to date, quarter to date, month to date

DAX
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
Sales QTD = TOTALQTD([Total Sales], 'Date'[Date])
Sales MTD = TOTALMTD([Total Sales], 'Date'[Date])

TOTALYTD(expression, dates [, filter] [, year_end_date]) evaluates the expression over all dates from the start of the year up to the last date in the current filter context. For a cell showing March 2025, that is 1 January to 31 March 2025.

For a fiscal year ending 30 June, pass the year-end as text:

DAX
Sales Fiscal YTD = TOTALYTD([Total Sales], 'Date'[Date], "6-30")

The equivalent long form, which is what the function does internally, uses DATESYTD:

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

Prior year with SAMEPERIODLASTYEAR

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

SAMEPERIODLASTYEAR takes the dates in the current context and shifts every one of them back exactly one year. For the cell March 2025, it returns March 2024. For the cell showing the year 2025, it returns all of 2024. Because it works from "the dates currently visible", the same measure works on a month, quarter or year axis.

A more general function is DATEADD, which can shift by any number of days, months, quarters or years:

DAX
Sales Previous Month = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))
Sales Two Years Ago  = CALCULATE([Total Sales], DATEADD('Date'[Date], -2, YEAR))

SAMEPERIODLASTYEAR(d) is equivalent to DATEADD(d, -1, YEAR). These functions need a contiguous date column and return a table of dates for CALCULATE to filter with.

Growth and difference

Once you have the current and prior measures, growth is plain arithmetic with DIVIDE:

DAX
Sales YoY Change = [Total Sales] - [Sales PY]

Sales YoY % =
VAR Previous = [Sales PY]
RETURN
    DIVIDE([Total Sales] - Previous, Previous)

DIVIDE returns blank when there is no prior-year value, so the first year simply shows nothing instead of an error. Format the measure as a percentage.

Moving averages with DATESINPERIOD

A rolling or moving average smooths seasonal spikes. DATESINPERIOD(dates, start, count, unit) returns a range of dates that starts from a given date and extends forward (positive count) or backward (negative count):

DAX
Sales Rolling 3M =
VAR LastDay = MAX('Date'[Date])
VAR Window  = DATESINPERIOD('Date'[Date], LastDay, -3, MONTH)
RETURN
    CALCULATE([Total Sales], Window)

Sales Rolling 3M Average = DIVIDE([Sales Rolling 3M], 3)

Here LastDay is the last date of the current cell; the window covers the three months that end on it. Dividing by 3 is correct only when all three months exist. At the start of your data the window is shorter, and a more careful version divides by the number of months actually present, DISTINCTCOUNT('Date'[Month Number])-style, or hides the result until enough history exists.

A related function, DATESBETWEEN(dates, start, end), takes explicit start and end dates, which is handy for fixed ranges such as "from the start of the year to today".

What the measures calculate, in pandas

The stand-in below uses 24 months of monthly sales (2024 and 2025). The pandas lines show the result each DAX measure produces. The mechanism is different: pandas works on a series by position and date, while DAX filters a date table.

Python YTD, prior year, growth and rolling 3 months

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the March 2025 row: the YTD is 120 + 115 + 135 = 370, the prior-year value is the March 2024 amount (120), and the three-month sum is also 370, because January to March is exactly the three-month window ending in March. For January 2025 the rolling window reaches back into the previous year (November 2024, December 2024, January 2025), because a rolling window ignores the year boundary while YTD resets at it.

Dates must be complete

There is one trap that catches many people: time intelligence relies on consecutive, complete dates. If a month is missing from your data (or your date table), shifting by position gives the wrong period. DAX avoids this by moving dates, not rows, but it still needs the date table to be complete. The next example shows the difference between shifting by position and shifting by date.

Python Position vs date: why gaps break shifts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

After the gap, shifting by 12 rows compares most months with the wrong one: January 2025 shows nothing, and February 2025 is compared with January 2024 (100) instead of February 2024 (101), one month off. Moving by date always finds the right month, and shows a blank only where the data really is missing. In Power BI, the cure is to give the model a complete date table with every day between the first and last date, rather than using the dates that happen to appear in the fact table.

Window functions: WINDOW, OFFSET and INDEX

Time intelligence functions need dates. Sometimes you need the same ideas on any ordered list: months, product ranks, ordered stages. A running total by product category, the previous row's value, "the best month in each year". The window functions (WINDOW, OFFSET, INDEX) are made for this. They return rows of a table by position in an ordered list, so they work on text, numbers and dates alike, and they do not need a marked date table.

All three take a relation (the table of rows to place in order), an ORDERBY (what defines the order) and optionally PARTITIONBY (restart the count in each group, for example each year) and MATCHBY (which columns identify a row).

DAX
Running Total =
CALCULATE (
    [Total Sales],
    WINDOW (
        1, ABS,
        0, REL,
        ALLSELECTED ( 'Date'[Month Number], 'Date'[Month Name] ),
        ORDERBY ( 'Date'[Month Number], ASC )
    )
)

Running Total in Year =
CALCULATE (
    [Total Sales],
    WINDOW (
        1, ABS, 0, REL,
        ALLSELECTED ( 'Date'[Year], 'Date'[Month Number], 'Date'[Month Name] ),
        ORDERBY ( 'Date'[Month Number], ASC ),
        PARTITIONBY ( 'Date'[Year] )
    )
)

Previous Month =
CALCULATE (
    [Total Sales],
    OFFSET ( -1, ALLSELECTED ( 'Date'[Month Number], 'Date'[Month Name] ),
             ORDERBY ( 'Date'[Month Number], ASC ) )
)

3 Month Average =
AVERAGEX (
    WINDOW ( -2, REL, 0, REL,
             ALLSELECTED ( 'Date'[Month Number], 'Date'[Month Name] ),
             ORDERBY ( 'Date'[Month Number], ASC ) ),
    [Total Sales]
)

Best Category =
MAXX (
    INDEX ( 1, ALLSELECTED ( Product[Category] ), ORDERBY ( [Total Sales], DESC ) ),
    Product[Category]
)

How to read them:

  • WINDOW(from, fromType, to, toType, relation, orderBy, ...) returns the rows between two positions. ABS means an absolute position counted from the start of the (partition of the) list (1 is the first row). REL means relative to the current row (0 is this row, -2 is two rows back). So WINDOW(1, ABS, 0, REL, ...) is "from the first row to the current one", a running total, and WINDOW(-2, REL, 0, REL, ...) is a rolling window of three rows.
  • OFFSET(delta, relation, orderBy, ...) returns the one row that is delta positions away: -1 is the previous row, 1 the next one. It is the clean way to write "compare with the previous item".
  • INDEX(position, relation, orderBy, ...) returns the row at an absolute position: 1 the first, -1 the last. With a measure in ORDERBY ( [Total Sales], DESC ), INDEX(1, ...) is the top item.
  • Every column used in ORDERBY and PARTITIONBY must be part of the relation, which is why the month number appears in the ALLSELECTED list. Include the month number and the month name so the visual's rows can be matched.

Compared with TOTALYTD and DATESINPERIOD, the window functions are more general and more explicit, but longer. Use the time intelligence functions when they fit exactly (YTD, same period last year) and the window functions for rolling windows, positional comparisons, running totals that restart on any partition and anything that is not a date. Desktop's visual calculations (functions such as RUNNINGSUM, MOVINGAVERAGE and PREVIOUS) are a friendlier layer on top of the same idea when your version has them.

Try it: the same ideas in pandas

The stand-in below uses cumsum, shift and rolling, which correspond to WINDOW(1, ABS, 0, REL), OFFSET(-1) and WINDOW(-2, REL, 0, REL). It also shows the trap: the relation decides what "previous" means.

Python Stand-in: running total, previous row and rolling average

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the last table, May's previous row is February, because the rows for March and April do not exist in this relation. In DAX the cure is the same as for time intelligence: build the relation from the date table ('Date'[Month Number]), which has every month, and not from a column of the fact table.

Practical advice

  • Build one base measure, then keep time measures small and named by what they show: Sales YTD, Sales PY, Sales YoY %.
  • Use the date table's Date column in time functions, never a date column of the fact table.
  • Mark the date table (Table tools > Mark as date table), or the functions may return wrong results in some situations.
  • Remember that YTD, prior-year and rolling measures can show values for future months that have no sales yet. Wrap them in an IF([Total Sales] <> 0, ...)-style guard (or compare to MAX of the actual data date) so the line chart does not run flat after today.
  • Test with a table visual, one month per row, to verify each measure against hand-calculated values before building charts. This is exactly the habit of the pandas check above.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which measure gives the same period of the previous year for any date selection?

    SAMEPERIODLASTYEAR shifts every date in the current context back one year, so it works on days, months, quarters and years alike.
  2. 2. How do you make year to date use a fiscal year ending on 30 June?

    TOTALYTD accepts an optional year-end date as text. DATESYTD has the same parameter.
  3. 3. What does DATESINPERIOD('Date'[Date], LastDay, -3, MONTH) return?

    A negative count looks backwards from the start date, so this is the three-month window ending on LastDay, as used in rolling averages.
  4. 4. Why should the date table have no missing days?

    Functions such as DATEADD and SAMEPERIODLASTYEAR need contiguous dates to move correctly, so build a complete calendar instead of using the dates found in sales.

Summary

  • Time intelligence measures wrap a base measure in CALCULATE and change only the date filter.
  • TOTALYTD, TOTALQTD and TOTALMTD accumulate from the start of the period; a year-end argument supports fiscal years.
  • SAMEPERIODLASTYEAR and DATEADD shift dates; growth is DIVIDE(current - previous, previous).
  • DATESINPERIOD builds rolling windows such as a three-month average.
  • WINDOW, OFFSET and INDEX pick rows by position in an ordered relation (running totals, previous row, top item) and work on any ordering, not only dates.
  • All of it needs a complete, marked date table; verify with a table visual before charting.

Next, ranking and sharing out totals: Ranking, Top N and Percent of Total.