ExamFinch.com My Study

Study guide

5.3 Time Intelligence Measures

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

5.3 Time Intelligence Measures

Key Takeaways Explain This Topic AI

Time intelligence requires a date table with a contiguous, gapless, unique date column marked as the date table.

TOTALYTD/QTD/MTD give period-to-date running totals; TOTALYTD accepts an optional fiscal year-end string.

SAMEPERIODLASTYEAR returns the same range one year back; it equals DATEADD(dates, -1, YEAR).

DATEADD shifts a date selection by any count and interval (DAY, MONTH, QUARTER, YEAR) in either direction.

PARALLELPERIOD returns the full parallel period (whole month/quarter/year) rather than a shifted partial range.

Last updated: June 2026

Quick Answer: Time intelligence DAX functions enable period-over-period analysis (YTD, YoY, QTD, moving averages). They all require a proper date table marked as such. Key functions: TOTALYTD for year-to-date, SAMEPERIODLASTYEAR for the prior year, DATEADD for flexible shifts, and PARALLELPERIOD for full parallel periods.

Prerequisites

Time intelligence requires a date table that is marked (Table Tools > Mark as Date Table), has a Date-typed column, has no gaps in the range (contiguous), has unique values (one row per date), and covers all dates in related facts. If any requirement fails, time intelligence returns wrong results or errors. The auto date/time hierarchy can also drive simple time intelligence, but a dedicated marked date table is the reliable, recommended approach.

  • Year-to-Date Revenue YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
  • Revenue YTD = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date])) // equivalent
  • Revenue Fiscal YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], "6/30") // FY ending June 30
  • Revenue QTD = TOTALQTD(SUM(Sales[Amount]), 'Date'[Date])
  • Revenue MTD = TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

The optional "MM/DD" argument on TOTALYTD sets the fiscal year-end; without it, TOTALYTD assumes a December 31 calendar year.

Year-Over-Year Revenue Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

  • YoY Change =
  • VAR Cur = SUM(Sales[Amount])
  • VAR Prior = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
  • RETURN Cur - Prior
  • YoY % Change =
  • VAR Cur = SUM(Sales[Amount])
  • VAR Prior = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
  • RETURN DIVIDE(Cur - Prior, Prior)
  • DATEADD vs. PARALLELPERIOD

DATEADD(dates, number, interval) shifts the current selection by any count and unit; SAMEPERIODLASTYEAR is exactly DATEADD(dates, -1, YEAR) . Revenue Prev Quarter = CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, QUARTER))

Revenue Prev Week = CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -7, DAY))

  • Function Behavior Current filter = March 15, 2026
  • DATEADD(-1, MONTH) Shifts the selection back Feb 15, 2026 (partial month)

PARALLELPERIOD(-1, MONTH) Returns the full parallel period Feb 1-28, 2026 (whole month) Revenue Full Prev Month = CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD('Date'[Date], -1, MONTH))

  • Running Totals and Moving Averages Running Total =
  • CALCULATE(
  • SUM(Sales[Amount]),
  • FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
  • )
  • Moving Avg 3M =
  • AVERAGEX(
  • DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH),
  • CALCULATE(SUM(Sales[Amount]))
  • )
  • Other Time Intelligence Functions
  • Function Purpose
  • FIRSTDATE / LASTDATE First/last date in the current context
  • STARTOFMONTH / ENDOFMONTH First/last day of the month
  • STARTOFYEAR / ENDOFYEAR First/last day of the year
  • PREVIOUSMONTH / NEXTMONTH Full range for the prior/next month
  • PREVIOUSYEAR / NEXTYEAR Full range for the prior/next year
  • DATESBETWEEN Custom range between two dates
  • DATESINPERIOD N intervals back from a reference date
  • Reusable Comparison Pattern Revenue = SUM(Sales[Amount])
  • Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
  • Revenue YTD = TOTALYTD([Revenue], 'Date'[Date])
  • Revenue PY YTD = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR('Date'[Date]))
  • YoY Growth = DIVIDE([Revenue] - [Revenue PY], [Revenue PY])
  • On the Exam

The PL-300 tests date-table requirements, TOTALYTD/QTD/MTD, SAMEPERIODLASTYEAR for YoY, the DATEADD vs. PARALLELPERIOD distinction, the fiscal year-end argument, and building both absolute and percentage YoY change.

Why a Dedicated Date Table Beats Auto Date/Time

Time intelligence functions internally call helpers like DATESYTD and SAMEPERIODLASTYEAR that need a continuous, complete sequence of dates to shift across. A real fact table has gaps, no sales on closed days, so you cannot anchor time intelligence to the fact's own date column. A dedicated date dimension with one row per calendar day, marked as the date table, gives the engine the gap-free spine it needs and lets you add fiscal calendars, custom hierarchies, and flags (weekday, holiday) that Auto Date/Time cannot.

This is why the very first troubleshooting step for "my YTD measure returns blanks or wrong totals" is almost always to confirm a proper, marked date table.

Reading a Time Intelligence Measure

Most time intelligence measures share one shape: CALCULATE(expression, <date filter function>) . The date function returns a table of dates that becomes the new filter context, and the expression evaluates over the fact rows that fall in those dates. SAMEPERIODLASTYEAR('Date'[Date]) returns last year's equivalent dates; DATESYTD('Date'[Date]) returns every date from the start of the year through the latest date in context; DATESINPERIOD returns a sliding window.

Once you see these functions as table-returning filters rather than magic, you can compose them, for example wrapping a YTD measure in SAMEPERIODLASTYEAR to get prior-year YTD.

Common Exam Traps

Watch for these recurring catches. TOTALYTD without a fiscal-year string assumes a December 31 year-end, so any fiscal-calendar scenario needs the "MM/DD" argument. DATEADD shifts the selected dates and can return a partial period, while PARALLELPERIOD always returns the whole period, choosing the wrong one is the most frequent time-intelligence error. And every time intelligence function silently misbehaves if the date column is not contiguous, so a question that mentions "missing dates" is almost always pointing you back to the date-table requirements.

Building Comparisons by Composition

The practical payoff of treating date functions as table filters is that you can stack them to build a full comparison suite from one base measure. Define Revenue = SUM(Sales[Amount]) , then Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])) , then Revenue YTD = TOTALYTD([Revenue], 'Date'[Date]) , and finally Revenue PY YTD = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR('Date'[Date])) . Growth measures are just DIVIDE([Revenue] - [Revenue PY], [Revenue PY]) .

Notice how each measure reuses the one below it rather than re-deriving the aggregation, this composition keeps the logic readable and is the reason calculation groups (which apply such patterns generically) are so valuable once the pattern count grows. On the exam, recognizing that a prior-year-YTD measure is simply YTD wrapped in SAMEPERIODLASTYEAR is the kind of compositional insight the questions probe.