Time Intelligence Measures

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 the date table. Key functions: TOTALYTD for year-to-date, SAMEPERIODLASTYEAR for same-period-last-year, DATEADD for flexible period shifts, and PARALLELPERIOD for full parallel periods.

Prerequisites

Time intelligence functions require a properly configured Date table:

1. ✅ Table marked as Date Table (Table Tools → Mark as Date Table)
2. ✅ Date column with Date data type
3. ✅ No gaps in the date range (contiguous dates)
4. ✅ Unique values (one row per date)
5. ✅ Covers all dates in related fact tables

Critical: If any of these requirements are not met, time intelligence functions will produce incorrect results or errors.

Year-to-Date (YTD)

TOTALYTD

Revenue YTD =
TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

Equivalent to:

Revenue YTD =
CALCULATE(
    SUM(Sales[Amount]),
    DATESYTD('Date'[Date])
)

Custom fiscal year end:

// Fiscal year ending June 30
Revenue Fiscal YTD =
TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], "6/30")

Quarter-to-Date and Month-to-Date

Revenue QTD =
TOTALQTD(SUM(Sales[Amount]), 'Date'[Date])

Revenue MTD =
TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

Year-Over-Year Comparisons

SAMEPERIODLASTYEAR

Returns the same date range from one year prior:

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

Year-Over-Year Change (Amount)

YoY Change =
VAR CurrentPeriod = SUM(Sales[Amount])
VAR PriorPeriod =
    CALCULATE(
        SUM(Sales[Amount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )
RETURN
    CurrentPeriod - PriorPeriod

Year-Over-Year Change (Percentage)

YoY % Change =
VAR CurrentPeriod = SUM(Sales[Amount])
VAR PriorPeriod =
    CALCULATE(
        SUM(Sales[Amount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )
RETURN
    DIVIDE(CurrentPeriod - PriorPeriod, PriorPeriod)

DATEADD — Flexible Period Shifts

DATEADD(dates, number_of_intervals, interval) shifts dates by any interval:

// Revenue from 1 year ago
Revenue Prev Year =
CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, YEAR))

// Revenue from 1 quarter ago
Revenue Prev Quarter =
CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, QUARTER))

// Revenue from 1 month ago
Revenue Prev Month =
CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, MONTH))

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

DATEADD vs. SAMEPERIODLASTYEAR:

• SAMEPERIODLASTYEAR = DATEADD(dates, -1, YEAR) — they are equivalent
• DATEADD is more flexible (any number of intervals, any direction)

PARALLELPERIOD vs. DATEADD

// Full previous month revenue
Revenue Full Prev Month =
CALCULATE(
    SUM(Sales[Amount]),
    PARALLELPERIOD('Date'[Date], -1, MONTH)
)

Running Totals and Moving Averages

Running Total (Cumulative)

Running Total =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

3-Month Moving Average

Moving Avg 3M =
AVERAGEX(
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH),
    CALCULATE(SUM(Sales[Amount]))
)

Other Time Intelligence Functions

Common Time Intelligence Patterns

Full Comparison Dashboard

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])

YTD Growth =
DIVIDE([Revenue YTD] - [Revenue PY YTD], [Revenue PY YTD])

On the Exam

The PL-300 frequently tests:

• Date table requirements for time intelligence to work
• TOTALYTD/QTD/MTD for period-to-date calculations
• SAMEPERIODLASTYEAR for year-over-year comparisons
• Difference between DATEADD and PARALLELPERIOD
• Creating YoY change (both absolute and percentage)