Time intelligence: date table, YTD and prior year

Comparing one period with another means swapping the date filter of the measure, and that is what the time intelligence functions do: they return a set of dates for CALCULATE to use as a filter.

They only work well on a date table. The date table must be continuous, with one row per day, no gaps, covering the whole range of the fact table. It must be marked as a date table and linked to Sales[Date] by a one-to-many relationship.

Revenue YTD =
TOTALYTD ( [Revenue], Calendar[Date] )

Revenue Prior Year =
CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( Calendar[Date] ) )

Change % =
DIVIDE ( [Revenue] - [Revenue Prior Year], [Revenue Prior Year] )

TOTALYTD ( [Revenue], Calendar[Date] ) is a shortcut for CALCULATE ( [Revenue], DATESYTD ( Calendar[Date] ) ). If the visual is at March 2025, SAMEPERIODLASTYEAR returns the dates of March 2024, and Revenue Prior Year adds up only that month's sales. To shift by another distance, DATEADD ( Calendar[Date], -1, YEAR ) also accepts QUARTER and MONTH.

Pitfall

Passing Sales[Date] to the time functions, or using a date table that only has the days on which there was a sale. The fact table column has repeated dates and missing days, and the period shift can come out wrong or raise an error. Always use the Calendar[Date] column.

To avoid it: build Calendar with every day from the first to the last year of the fact table, mark it as a date table and use its columns (year, month) in the visuals, never the fact table's.