What is the “Standard Time-Related Calculations” pattern?
A DAX pattern that uses built-in time intelligence functions (e.g. DATESYTD, DATEADD, etc.) over a standard Gregorian calendar (Jan–Dec, fixed quarters, etc.) to compute things like YTD, period-over-period, moving totals, moving averages, etc.
Q: What are the requirements for the Date table in this pattern?
Should cover a continuous date range (start Jan 1, end Dec 31, include all days);
Have a unique Date column (no time component)
Marked as a Date Table in the model (so DAX can apply REMOVEFILTERS automatically when time intelligence functions run)
Q: When should you consider using custom time-related calculations instead of the standard pattern?
A: When your business calendar doesn’t follow a standard Gregorian layout (e.g. fiscal year starting in March, 4-4-5 week calendars, week-based calendars, etc.), or when built-in functions fail to meet your requirement.
Q: In the naming convention used by the pattern, what does “QTD” stand for?
A: Quarter-to-Date — the aggregate of a measure from the start of the quarter up to the current date in context.
Q: What is “MAT” in this pattern?
A: Moving Annual Total — the rolling total of the measure over the last 12 months (or last year window) relative to the current date.
Q: What does “PY” stand for, and what is its role?
A: Previous Year — used for shifting measures (e.g. to compute same period last year). The measure labeled PY returns the value of the measure in the prior year period, adjusted for filter context.
Q: What is “POP” and when would you use it?
Period-over-Period — a dynamic comparison measure that automatically switches between year-over-year, quarter-over-quarter, or month-over-month depending on the grain (i.e. the “scope”) of the report.
Sales POP :=
SWITCH ( TRUE,
ISINSCOPE ( ‘Date’[Year Month] ), [Sales MOM],
ISINSCOPE ( ‘Date’[Year Quarter] ), [Sales QOQ],
ISINSCOPE ( ‘Date’[Year] ), [Sales YOY] )
Q: How is “YTDOPY” used or interpreted?
A: Year-to-date over previous year — compares the YTD value in the current year against the full value of the prior year, to see how the current year’s accumulation stacks up to a full prior year.
Q: Write a basic Sales YTD measure using CALCULATE and DATESYTD.
Sales YTD :=
IF (
[ShowValueForDates],
CALCULATE ( [Sales Amount], DATESYTD ( ‘Date’[Date] ) )
)
Q: How would you write Sales QTD similarly?
Sales QTD :=
IF (
[ShowValueForDates],
CALCULATE ( [Sales Amount], DATESQTD ( ‘Date’[Date] ) )
)
Q: Show the Sales MTD formula.
Sales MTD :=
IF (
[ShowValueForDates],
CALCULATE ( [Sales Amount], DATESMTD ( ‘Date’[Date] ) )
)
Q: How to compute Sales PY (Previous Year) using DATEADD or similar?
Sales PY :=
IF (
[ShowValueForDates],
CALCULATE (
[Sales Amount],
CALCULATETABLE (
DATEADD ( ‘Date’[Date], -1, YEAR ),
‘Date’[DateWithSales] = TRUE()
)
)
)
Q: Write Sales YOY % (year-over-year percentage) given existing Sales and Sales PY measures.
Sales YOY % :=
DIVIDE ( [Sales Amount] - [Sales PY], [Sales PY] )
Q: Provide a Moving Annual Total (MAT) measure.
Sales MAT :=
IF (
[ShowValueForDates],
CALCULATE (
[Sales Amount],
DATESINPERIOD ( ‘Date’[Date], MAX ( ‘Date’[Date] ), -1, YEAR )
)
)
Q: How would you compute the Moving Annual Total Growth % (MATG) in this pattern?
Sales MAT Growth % :=
DIVIDE (
[Sales MAT] - CALCULATE ( [Sales MAT], DATEADD ( ‘Date’[Date], -1, YEAR ) ),
CALCULATE ( [Sales MAT], DATEADD ( ‘Date’[Date], -1, YEAR ) )
)
the moving annual total is usually computed at the month or day granularity to show trends in a chart
Q: Write a formula for 30-day rolling average using AVERAGEX + DATESINPERIOD.
Sales 30D Avg :=
VAR DateWindow =
DATESINPERIOD ( ‘Date’[Date], MAX ( ‘Date’[Date] ), -30, DAY )
VAR RowCount = COUNTROWS ( DateWindow )
RETURN
IF (
RowCount >= 7, – guard for enough days
AVERAGEX ( DateWindow, [Sales Amount] )
)
Q: What does DATESYTD(‘Date’[Date]) return?
A: All dates from January 1 of the current year to the last date visible in the filter context.
Q: What function gives all dates in the previous year?
A: SAMEPERIODLASTYEAR(‘Date’[Date]) or equivalently DATEADD(‘Date’[Date], -1, YEAR)
Q: What is the difference between DATEADD and SAMEPERIODLASTYEAR?
DATEADD shifts a date range by any number of intervals (days, months, years, etc.).
SAMEPERIODLASTYEAR only shifts by exactly one year and preserves irregular month lengths.
Q: What’s the difference between ENDOFMONTH and EOMONTH?
ENDOFMONTH → returns a table (time intelligence).
EOMONTH → returns a scalar date (use in calculations, not filters).
Q: What’s the difference between PREVIOUSDAY and subtracting 1 from a date?
PREVIOUSDAY → returns a table (for filters). This function is reliable even when your data source has gaps (e.g., no sales on a specific day). It will correctly identify the previous date based on the calendar table, rather than just the previous entry in your data table
Date - 1 → returns a scalar (for row or value calculations).
Q: When should you use EDATE instead of DATEADD?
A: When shifting a single date value by a number of months (e.g., invoice due dates). DATEADD shifts entire sets of dates in filters.
EDATE(< start_date >, < months >)
Q: What does DATESBETWEEN do?
A: Returns a table of dates between two specified boundary dates.
What does TOTALYTD do?
Returns the year-to-date total of a measure using a date column; it’s equivalent to CALCULATE([Measure], DATESYTD(‘Date’[Date])).
