6. Time Intelligence: YTD, Prior Year, Growth

Time intelligence functions work with the date table marked in the model (here "Calendar", related to Sales[Date]). A real model's calendar contains every day in a row; in the trainer it holds only the sales days, so the sheet fits on screen.

• TOTALYTD([Revenue], Calendar[Date]) — a running total from the start of the year to the last date of the row;
• CALCULATE([Revenue], SAMEPERIODLASTYEAR(Calendar[Date])) — the same dates one year earlier;
• DATEADD(Calendar[Date], -1, MONTH) — shift by a month (or YEAR, QUARTER);
• year-over-year growth: DIVIDE([Revenue] - [Prior Year], [Prior Year]) — for 2025 it is blank because there is nothing to compare with.

Put Calendar[Period] in the PivotTable rows to see the months in order.

In the trainer the model is already built: the Sales fact table on the first sheet, and the Products, Stores and Calendar lookup tables on the "Lookups" sheet. The relationships are shown in the Power Pivot panel under the sheet (expand "Data model"). The "+ Measure" button is there too: the name goes on the left, the formula on the right, and Enter saves it. Below is a PivotTable of your measures; change its rows with the "PivotTable rows" list.

The argument separator is a comma, as in English Excel (a semicolon also works if you use one throughout the formula).