Advanced Time Intelligence Functions

Time intelligence functions allow you to compare data across time periods — a core requirement in business reporting.

Pre-requisites for Time Intelligence

1. A dedicated Date table must exist in the model
2. The Date table must have a column marked as the Date column
3. The Date table must contain continuous dates (no gaps)
4. The Date table must be related to the fact table

Creating a Date Table:

DateTable =
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Day", DAY([Date]),
    "Weekday", FORMAT([Date], "dddd")
)

SAMEPERIODLASTYEAR()

Returns the same dates from the previous year.

Syntax: SAMEPERIODLASTYEAR(Dates)

Example:

Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))

Year-Over-Year Growth:

YoY Growth =
VAR CurrentYear = [Total Sales]
VAR LastYear = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
RETURN
DIVIDE(CurrentYear - LastYear, LastYear)

PARALLELPERIOD()

Returns a set of dates shifted by a specified number of intervals.

Syntax: PARALLELPERIOD(Dates, NumberOfIntervals, Interval)

Examples:

Sales Last Quarter = CALCULATE([Total Sales], PARALLELPERIOD(Dates[Date], -1, QUARTER))

Sales Two Years Ago = CALCULATE([Total Sales], PARALLELPERIOD(Dates[Date], -2, YEAR))

YTD, MTD, QTD — Period-to-Date Functions

These functions calculate cumulative totals from the start of the period to the current date.

Examples:

YTD Sales = TOTALYTD([Total Sales], Dates[Date])

MTD Sales = TOTALMTD([Total Sales], Dates[Date])

QTD Sales = TOTALQTD([Total Sales], Dates[Date])

Using CALCULATE + DATESYTD (alternative approach):

YTD Sales = CALCULATE([Total Sales], DATESYTD(Dates[Date]))

PREVIOUSMONTH(), PREVIOUSQUARTER(), PREVIOUSYEAR()

Example:

Previous Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH(Dates[Date]))

Time Intelligence Summary Table