Time Intelligence Functions0%
Time Intelligence Functions
Beginner12 min read•Updated: Jul 11, 2026
Time Intelligence Functions in DAX: YTD, MTD, QTD & YoY Growth
Time Intelligence calculations are among the most frequent requirements in business reporting. Executives need to compare current sales against prior months, calculate fiscal year-to-date figures, and analyze Year-over-Year (YoY) percentage growth.
1. The Date Table Prerequisite (Golden Rule)
CRITICAL RULE: For DAX Time Intelligence functions (
TOTALYTD, SAMEPERIODLASTYEAR, DATEADD) to work accurately, your model MUST contain a dedicated Calendar / Date Table that fulfills these four strict criteria:
1. It must contain a column of data type Date with continuous dates (no missing days).
2. It must span full years covering all transaction dates in your fact tables.
3. It must be marked as an official Date Table (Mark as Date Table in Power BI Desktop).
4. It must connect to your Fact table via a 1-to-Many ($1 : *$) relationship.Generating a Calendar Table in DAX
DAX
Dim_Date =
VAR MinYear = YEAR(MIN(Fact_Sales[OrderDate]))
VAR MaxYear = YEAR(MAX(Fact_Sales[OrderDate]))
RETURN
ADDCOLUMNS(
CALENDAR(DATE(MinYear, 1, 1), DATE(MaxYear, 12, 31)),
"Year", YEAR([Date]),
"MonthNumber", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM"),
"Quarter", "Q" & FORMAT([Date], "Q"),
"DayOfWeek", FORMAT([Date], "dddd")
)
2. Cumulative Period Functions: YTD, QTD, MTD
Cumulative functions aggregate values from the beginning of a period up to the current date in context:
Syntax & Implementations
DAX
-- 1. Year-To-Date (YTD) Sales
Sales YTD =
TOTALYTD(
[Total Sales],
Dim_Date[Date]
)
-- 2. Quarter-To-Date (QTD) Sales
Sales QTD =
TOTALQTD(
[Total Sales],
Dim_Date[Date]
)
-- 3. Month-To-Date (MTD) Sales
Sales MTD =
TOTALMTD(
[Total Sales],
Dim_Date[Date]
)
3. Prior Period Comparisons & Year-over-Year (YoY)
Comparing metrics against historical benchmarks is straightforward using SAMEPERIODLASTYEAR and DATEADD.
DAX
-- Prior Year Sales (Same Period Last Year)
Sales Prior Year =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(Dim_Date[Date])
)
-- Year-over-Year (YoY) Sales Variance Amount
Sales YoY Variance =
[Total Sales] - [Sales Prior Year]
-- Year-over-Year (YoY) Growth Percentage
Sales YoY Growth % =
DIVIDE(
[Sales YoY Variance],
[Sales Prior Year],
0
)
Output
Period Comparison Matrix:
Month Total Sales Sales Prior Year Sales YoY Variance YoY Growth %
2025-Jan $100,000 $80,000 +$20,000 +25.0%
2025-Feb $120,000 $110,000 +$10,000 +9.1%
2025-Mar $140,000 $150,000 -$10,000 -6.7%
4. Custom Shifts with DATEADD
While SAMEPERIODLASTYEAR shifts dates back by exactly 1 year, DATEADD offers full flexibility:
DAX
-- Prior Month Sales
Sales Prior Month =
CALCULATE(
[Total Sales],
DATEADD(Dim_Date[Date], -1, MONTH)
)
-- Prior Quarter Sales
Sales Prior Quarter =
CALCULATE(
[Total Sales],
DATEADD(Dim_Date[Date], -1, QUARTER)
)
Interactive Quiz
Multiple Choice Questions
Question 1
Which mandatory requirement must be satisfied for DAX Time Intelligence functions to calculate correctly?
Question 2
If a company's fiscal year starts on April 1st instead of January 1st, how do you adjust
TOTALYTD?Question 3
What is the difference between
SAMEPERIODLASTYEAR(DateCol) and DATEADD(DateCol, -1, YEAR)?Question 4
What does the DAX measure
[Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dim_Date[Date])) compute?Question 5
Why should you use
DIVIDE([YoY Variance], [Sales Prior Year], 0) rather than the standard / division operator?Next Lesson
Iterators (SUMX, AVERAGEX)
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
| Previous Lesson | Next Lesson |
|---|---|
| Project requirements for Intermediate Level | Iterators (SUMX, AVERAGEX) |
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.