<!-- https://learnsql.cpetoday.com/analyze/time-intelligence -->
# Time intelligence in DAX

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.

> Diagram: A month strip for 2024 and 2025 with June 2025 selected. Year to date covers January to June 2025, prior year to date covers January to June 2024, and rolling 12 months covers July 2024 to June 2025.

## 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`, `CLOSINGBALANCEMONTH` and 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 `COALESCE` only 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).
