This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
--> Create model calculations by using DAX
--> Implement Time Intelligence Measures
Note that there are 10 practice questions (with answers and explanations) at the end of each topic. Also, there are 2 practice tests with 60 questions each available on the hub below all the exam topics.
Exam Context
Time intelligence is a core DAX competency on the PL-300 exam. Microsoft frequently tests a candidate’s ability to calculate values across time, such as year-to-date, prior period comparisons, rolling totals, and growth metrics.
What Are Time Intelligence Measures?
Time intelligence measures are DAX calculations that:
- Analyze data over time
- Compare values across different periods
- Accumulate results over a date range
These measures rely on:
- A proper date table
- Correct relationships
- The
CALCULATEfunction
Prerequisites for Time Intelligence (Frequently Tested)
Before time intelligence will work correctly, the model must include:
1. A Dedicated Date Table
- One row per date
- Continuous date range (no gaps)
- Marked as a Date table in Power BI
2. Proper Relationships
- Date table related to fact tables
- Relationship uses the date column (not datetime, if possible)
3. Correct Data Types
- Date column must be of type Date
- Not text or integer
⚠️ Exam Tip:
Many PL-300 questions are trick questions where time intelligence fails because one of these prerequisites is missing.
Role of CALCULATE in Time Intelligence
All built-in time intelligence functions work by modifying filter context using CALCULATE.
Example:
Sales YTD =
CALCULATE(
[Total Sales],
DATESYTD(Date[Date])
)
👉 CALCULATE changes the filter context to include all dates from the start of the year through the current date.
Common Time Intelligence Functions (PL-300 Focus)
Year-to-Date (YTD)
Sales YTD =
CALCULATE(
[Total Sales],
DATESYTD(Date[Date])
)
Month-to-Date (MTD)
Sales MTD =
CALCULATE(
[Total Sales],
DATESMTD(Date[Date])
)
Quarter-to-Date (QTD)
Sales QTD =
CALCULATE(
[Total Sales],
DATESQTD(Date[Date])
)
Previous Period Comparisons
Previous Year
Sales PY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(Date[Date])
)
Previous Month
Sales PM =
CALCULATE(
[Total Sales],
DATEADD(Date[Date], -1, MONTH)
)
Exam Insight:SAMEPERIODLASTYEAR requires a continuous date table—a common failure point on the exam.
Rolling and Moving Averages
Rolling 12 Months
Sales Rolling 12M =
CALCULATE(
[Total Sales],
DATESINPERIOD(
Date[Date],
MAX(Date[Date]),
-12,
MONTH
)
)
This pattern is commonly tested in scenario-based questions.
Growth and Variance Measures
Year-over-Year Growth
Sales YoY Growth =
[Total Sales] - [Sales PY]
Year-over-Year Percentage
Sales YoY % =
DIVIDE(
[Total Sales] - [Sales PY],
[Sales PY]
)
⚠️ Exam Tip:
Always use DIVIDE() instead of / to safely handle divide-by-zero scenarios.
Time Intelligence vs Custom Date Logic
| Built-in Time Intelligence | Custom Logic |
|---|---|
| Requires date table | Can work without one |
| Simpler syntax | More flexible |
| Optimized by engine | More complex |
| Preferred for PL-300 | Tested less often |
👉 For PL-300, Microsoft prefers built-in time intelligence functions.
Common Mistakes (Often Tested)
- Using time intelligence without marking a date table
- Using text-based dates
- Missing dates in the calendar
- Using fact table dates instead of a shared date dimension
- Expecting time intelligence to work in calculated columns
Best Practices for PL-300 Candidates
- Always create and mark a common date table
- Build reusable base measures
- Use built-in time intelligence when possible
- Validate results at different grain levels (year, month, day)
- Avoid time intelligence in calculated columns
How This Appears on the Exam
Expect questions that:
- Ask why a YTD or PY measure returns incorrect results
- Test which function to use for a specific time comparison
- Require selecting the correct DAX pattern
- Identify missing prerequisites in a data model
Key Takeaways
- Time intelligence is a high-value exam topic
- Depends on a proper date table and relationships
- Uses
CALCULATEto modify filter context - Enables YTD, PY, rolling totals, and growth analysis
- Frequently appears in scenario-based questions
Practice Questions
Go to the Practice Exam Questions for this topic.
The Data Community 