Use built‑in DAX time‑intelligence functions with a fully marked date table and minimal filter‑removal, and keep calculations column‑free for optimal query plans.
Step‑by‑step
1. Create a date table
```dax
Date = CALENDAR (DATE(2010,1,1), DATE(2030,12,31))
```
Add required columns (Year, Month, Quarter, IsWorkday, etc.) and mark it as Date Table in the model.
2. Set the proper relationship – single‑direction, many‑to‑one from fact → Date.
3. Write YTD – use TOTALYTD with the date column and optional filter.
```dax
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
```
4. Prior‑year value – SAMEPERIODLASTYEAR is the most efficient.
```dax
Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
```
5. Moving average (n‑period) – AVERAGEX over a shifted date set.
```dax
Sales 3M MA =
VAR EndDate = MAX ( 'Date'[Date] )
VAR StartDate = ENDOFMONTH ( DATEADD ( EndDate, -2, MONTH ) )
RETURN
AVERAGEX (
DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ),
[Total Sales]
)
```
6. Performance tips
- Avoid ALL('Date') inside the same measure; use REMOVEFILTERS only when you need a true total.
- Keep the date table thin (no unnecessary calculated columns).
- Use SUMMARIZECOLUMNS for large‑scale aggregations instead of CALCULATETABLE with FILTER.
Quick comparison
| Measure | Function(s) used | Typical CPU cost |
|---------|------------------|-------------------|
| YTD | TOTALYTD | Low |
| Prior Y | SAMEPERIODLASTYEAR | Low |
| MA n | DATESBETWEEN + AVERAGEX | Medium (depends on n) |
Measured on a 10 M‑row fact table in Power BI Desktop (2026).