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
- One row for every day, with no gaps, covering every date in your fact tables (and ideally whole years). The Date column must be Date or DateTime type with no time portion and no duplicates.
- Relate it (one to many) to the date column of each fact table. In Power BI, Mark as date table (Table tools) with that column; in Power Pivot use Design > Mark as Date Table. If you mark it, the built-in time intelligence functions get a guarantee of a contiguous date column.
- Never use the auto date/time hidden tables for serious work: turn the option off (Options > Data load) and build one shared Calendar.
- Month names need a sort column (Month Number) so January sorts before April: set Sort by column on Month. Do the same for Year-Month text (sort by a YearMonthKey integer) and weekday names.
- Build it in Power Query (M) or in DAX. DAX version:
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.
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)
FIRSTDATE,LASTDATE,STARTOFMONTH,ENDOFMONTH,STARTOFYEAR,ENDOFYEAR,OPENINGBALANCEMONTH,CLOSINGBALANCEMONTHand the quarter/year variants.- Inventory, bank balances, headcount do not add up over time. Use the last value:
Closing Stock = CALCULATE(SUM(Stock[Units]), LASTDATE('Calendar'[Date]))or, if the last day may have no row,CALCULATE(SUM(Stock[Units]), LASTNONBLANK('Calendar'[Date], CALCULATE(COUNTROWS(Stock)))).
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
- Week based (52/53 week): add a Week Number and a Week Year column in the Calendar and compare with
CALCULATE([Total Sales], ALL('Calendar'), VALUES('Calendar'[Week]), 'Calendar'[Year] = MAX('Calendar'[Year]) - 1)style filters. DATEADD by 365 days drifts. - Working days: add an IsWorkday flag (and holidays) to the Calendar, then
CALCULATE(COUNTROWS('Calendar'), 'Calendar'[IsWorkday] = TRUE()). - Month over month when a month has no data: DATEADD returns blank for a missing prior period; show 0 with
COALESCEonly if appropriate. - To date, but only up to the last date that has data:
VAR LastSale = CALCULATE(MAX(Sales[OrderDate]), ALL(Sales)), then filter the Calendar to dates <= LastSale so the current year is not compared with a full prior year.
Pitfalls
- The date column you pass must be the Calendar's, not the fact table's.
- Time intelligence functions return a table of dates, so they belong in CALCULATE filters (or as the iterator table of an X function).
- If you pass Year and Month-name text columns instead of a date, the functions do nothing useful. A measure that is blank in every row usually means the relationship to the Calendar is missing or the Calendar has no matching dates.
- Calculation groups are the clean way to give every measure YTD/PY/YoY variants (see the patterns topic).
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.