What Are DAX Time Intelligence and Related Functions in Power BI?
DAX time intelligence functions — such as TOTALYTD(), SAMEPERIODLASTYEAR(), and DATEADD() — shift the date context of a calculation to compare periods like this year vs. last year. The RELATED() function is a separate DAX tool that pulls a column value from a related table into the current row’s context.
DAX Time Intelligence Functions
- TOTALYTD() / TOTALQTD() / TOTALMTD(): calculates year-, quarter-, or month-to-date totals.
- SAMEPERIODLASTYEAR(): shifts the date context back one year for comparison.
- DATEADD(): shifts dates forward or backward by a specified interval (day, month, year).
- DATESYTD() / DATESBETWEEN(): returns a table of dates for a year-to-date range or a custom range.
- PARALLELPERIOD(): returns a set of dates shifted by a full period, such as the previous quarter.
Why Time Intelligence Needs a Proper Date Table
Time intelligence functions require a proper Date table marked as the model’s official date table (Model View → Mark as Date Table); without this, functions like SAMEPERIODLASTYEAR() can return inconsistent results even on correctly structured data.
The RELATED Function in DAX
RELATED() pulls a column value from a related table into the current row context, typically used inside a calculated column on the “many” side of a one-to-many relationship — for example, pulling a Category name from a Products table into a row of a Sales table.
RELATED vs. RELATEDTABLE
While RELATED() returns a single value from the “one” side of a relationship, RELATEDTABLE() does the reverse — it returns every matching row from the “many” side when starting from the “one” side, and is typically used inside an aggregation function.
How to Calculate Year-over-Year Growth with DAX Time Intelligence
This 6-step tutorial builds a year-over-year growth measure using SAMEPERIODLASTYEAR(), the most common real-world time intelligence use case in Power BI.
Prerequisites
- A properly marked Date table in the model
- A base sales-style measure already created
Steps
- Confirm the Date table is marked: Model View → right-click the Date table → Mark as Date Table (skip if already done).
- Create a base measure: Total Sales = SUM(Sales[Amount]).
- Build the prior-year measure: PY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(‘Date'[Date])).
- Calculate growth percentage: YoY Growth % = DIVIDE([Total Sales] – [PY Sales], [PY Sales]).
- Format the result as a percentage: Use the Modeling ribbon to set the measure’s format to Percentage.
- Add both measures to a line chart: Plot Total Sales and PY Sales over time to visually confirm the comparison looks correct.
