Intro to Time Intelligence

Standard DAX measures respond to whatever filters are active — a slicer set to February returns February's total. Time intelligence goes further: it answers questions that require stepping outside the current filter and comparing it to a different period automatically.

Why Time Intelligence Requires a Dedicated Dates Table

The Sales table contains an Order Date column. It might seem logical to run time intelligence functions directly against it — but this does not work reliably, and the reason is fundamental to how time intelligence functions operate.

• Sales table — Order Date: contains only dates when orders were placed. Gaps exist throughout the calendar;
• Dates table — Date: one row for every calendar day without exception — a complete, continuous calendar. No gaps. This is what time intelligence functions require.

Marking the Dates Table

Before any time intelligence function will work, the dates table must be formally declared as the model's official date axis. Power Pivot validates the column for gaps and duplicates at this point — if any are found, an error is thrown and must be fixed before continuing.

1. Open Power Pivot → Manage and navigate to the Dates table in grid view;
2. Go to the Design tab in the Power Pivot ribbon;
3. Click Mark as Date Table → Mark as Date Table;
4. Select the Date column from the dropdown and click OK;
5. If no error appears, the table is validated and ready — time intelligence functions can now reference it.

The Time Intelligence Measures

1. YTD — Year to date

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

When the pivot table is filtered to a specific month, Total Sales returns that month only. Total Sales YTD expands the filter to run from 1 January of that year through the last date in context, accumulating every month up to and including the current one.

2. LY — Same period last year

Total Sales LY := CALCULATE(SUM(Sales[Total]), SAMEPERIODLASTYEAR(Dates[Date]))

When the pivot table shows February 2025, Total Sales LY returns February 2024. If no prior year data exists in the model, the measure returns blank — which is correct behaviour, not an error.

3. YoY Growth % — Year-on-year growth

YoY Growth % := DIVIDE([Total Sales] - [Total Sales LY], [Total Sales LY])

Combines the two preceding measures — both referenced by name, not recalculated inline. If either Total Sales or Total Sales LY is updated, YoY Growth % picks up the change automatically. Format this measure as a percentage in Power Pivot.

Task

Step 1 - Test your measures in a PivotTable

• Go to Insert → PivotTable → Use this workbook's Data Model → OK.
• Add MonthName (from the Dates table) to the Rows area.
• Add Total Sales, Total Sales YTD, Total Sales LY, and YoY Growth % (all from the Sales table) to the Values area.