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 CALCULATE function

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

👉 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 CALCULATE to 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.