Time comparisons — month-over-month (MoM) and year-over-year (YoY) — are in nearly every report, yet everyone rewrites them from scratch. Here is a template you can paste, rename, and reuse. It assumes a marked Date table with a single active relationship to your fact.
The base measure
Every pattern below builds on one base measure. Name it to match your fact:
Total Sales = SUM ( Fact[SalesAmount] )
MoM (previous month)
Sales PM =
CALCULATE (
[Total Sales],
DATEADD ( 'Date'[Date], -1, MONTH )
)
MoM % change
Sales MoM % =
VAR Current = [Total Sales]
VAR Previous = [Sales PM]
RETURN
DIVIDE ( Current - Previous, Previous )
DIVIDE returns blank instead of an error when Previous is zero — use it, do not use /.
YoY (same period last year)
Sales PY =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
YoY % change
Sales YoY % =
VAR Current = [Total Sales]
VAR Previous = [Sales PY]
RETURN
DIVIDE ( Current - Previous, Previous )
YTD and the rolling versions
If you also need year-to-date and a rolling 12-month, see dax-time-intelligence-ytd-qtd-mtd and rolling-averages-moving-sums-dax.
Why DATEADD not PREVIOUSMONTH
PREVIOUSMONTH is fine, but DATEADD is more flexible — change MONTH to QUARTER or YEAR and you get the quarter/annual comparison for free. One function, three comparisons.
Gotcha: no marked date table
If 'Date'[Date] is not marked as a date table, time intelligence can return wrong totals on the grand total row. Right-click the table → Mark as Date Table.
FAQ
Q: My YoY shows blank for the first year — is that wrong?
A: No. There is no previous year, so SAMEPERIODLASTYEAR returns blank, and DIVIDE keeps it blank instead of dividing by nothing.
Q: MoM works but YoY doesn’t — why? A: Usually the date table doesn’t span a full extra year, or the relationship is inactive. Confirm the Date table covers at least last year’s full range.
Q: Can I compare to a custom fiscal year?
A: Yes — use DATESYTD with a FiscalYearEndDate argument, or build a fiscal date table. Standard SAMEPERIODLASTYEAR follows the calendar.
Microsoft documents this feature in its Time intelligence functions reference.
This template, plus the date table it assumes, is packaged in the Time Intelligence Starter Pack.