Downloads

Time intelligence

Date tables, year to date, prior year, rolling 12 months and fiscal calendars.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Build a date table that works with the time intelligence functions.
  • Calculate year to date, prior year and growth measures.
  • Build rolling periods and running totals.
  • Handle fiscal calendars and period-end balances correctly.

Time intelligence compares and accumulates over dates: year to date, prior year, year over year, rolling 12 months, fiscal periods. It needs a proper date table.

The date table requirements

Calendar =
VAR MinDate = DATE(YEAR(MIN(Sales[OrderDate])), 1, 1)
VAR MaxDate = DATE(YEAR(MAX(Sales[OrderDate])), 12, 31)
RETURN
ADDCOLUMNS(
    CALENDAR(MinDate, MaxDate),
    "Year", YEAR([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "Month Number", MONTH([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Year-Month", FORMAT([Date], "yyyy-MM"),
    "Weekday Number", WEEKDAY([Date], 2),
    "Weekday", FORMAT([Date], "dddd"),
    "Fiscal Year", "FY" & (YEAR([Date]) + IF(MONTH([Date]) >= 7, 1, 0))
)

CALENDARAUTO() builds a calendar from all date columns in the model; prefer an explicit range so stray dates do not widen it.

J F M A M J J A S O N D J F M A M J J A S O N D 2024 2025 Prior year to date Year to date Rolling 12 months: Jul 2024 to Jun 2025 Highlighted: the selected date, June 2025.
The same date window, shifted. Year to date and prior year to date cover the same months in two years.

Year, quarter and month to date

Sales YTD = TOTALYTD([Total Sales], 'Calendar'[Date])
Sales YTD (fiscal June) = TOTALYTD([Total Sales], 'Calendar'[Date], "6/30")
Sales QTD = TOTALQTD([Total Sales], 'Calendar'[Date])
Sales MTD = TOTALMTD([Total Sales], 'Calendar'[Date])
-- Same thing written with CALCULATE, which also works for anything TOTALYTD cannot do:
Sales YTD = CALCULATE([Total Sales], DATESYTD('Calendar'[Date]))

The optional last argument of TOTALYTD / DATESYTD is the year end date as text, for fiscal years ("6/30", "3/31"). The functions use the Calendar date column, never the fact table's date column.

Prior period and growth

Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
Sales PY (alt) = CALCULATE([Total Sales], DATEADD('Calendar'[Date], -1, YEAR))
YoY $ = [Total Sales] - [Sales PY]
YoY % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])
Sales PM = CALCULATE([Total Sales], DATEADD('Calendar'[Date], -1, MONTH))
Sales PQ = CALCULATE([Total Sales], DATEADD('Calendar'[Date], -1, QUARTER))

DATEADD shifts the dates in the current context by an interval; PARALLELPERIOD returns the whole period (not just the visible days). Both need contiguous dates in the context, so a matrix that puts Quarter on rows and Year on columns can fail with "contiguous dates"; put Year then Quarter on rows. When the current period is incomplete (a partial current year), SAMEPERIODLASTYEAR compares the same days, which is what you usually want.

Rolling periods

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

Sales Rolling 3M Avg =
AVERAGEX(DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH), [Total Sales])    -- average of daily values: usually not what you want

Monthly Avg Rolling 3M =
VAR Months = DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH)
RETURN DIVIDE(CALCULATE([Total Sales], Months), 3)

DATESBETWEEN(Calendar[Date], start, end) is the general form; DATESINPERIOD(dates, startDate, number, interval); intervals are DAY, MONTH, QUARTER, YEAR. Use LASTDATE/MAX of the date column as the anchor so the window ends at the last visible day.

Cumulative (running) total

Running Total =
CALCULATE([Total Sales], FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date])))
-- Over visible dates only (respects a year slicer):
Running Total (selected) =
CALCULATE([Total Sales], FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date])))

Use a unique date or month key in the axis. A running total that restarts each year: add 'Calendar'[Year] = MAX('Calendar'[Year]) to the filter or use DATESYTD.

Period boundaries and balances (semi-additive measures)

Fiscal calendars

Add Fiscal Year, Fiscal Quarter and Fiscal Month Number columns to the Calendar (the examples above), sort them properly, and give TOTALYTD the fiscal year end. Do not hard code a start month in several measures; put it in the Calendar columns.

Same period comparisons that need care

Pitfalls

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.