DAX II & Time Intelligence

Learning objectives

By the end you can:

1. Explain from first principles why time intelligence requires a dedicated, contiguous, marked Date table, and build one (either generated inside Power Pivot or created and marked from your own calendar) flagging the correct date column with Mark as Date Table.
2. Write a running total to date with TOTALYTD (and its sibling DATESYTD inside CALCULATE), including the optional year_end_date argument that switches a calendar year into an Indian fiscal year ending 31 March.
3. Fetch a prior-period value with SAMEPERIODLASTYEAR and with the more general DATEADD, and state precisely when DATEADD and PARALLELPERIOD differ from SAMEPERIODLASTYEAR.
4. Build a year-over-year (YoY) measure and a YoY % measure, guarding the division with DIVIDE so a missing prior year returns blank rather than an error.
5. Use the iterators SUMX and AVERAGEX to compute a per-row quantity (a weighted price, a line-extended revenue, a per-store average) and explain how their row context differs from the plain SUM/AVERAGE aggregators.
6. Refactor a long measure with the VAR/RETURN construct so it is readable and faster, and explain the rule that a VAR is evaluated exactly once, in the filter context where it is declared.
7. Assemble a small set of KPI measures (YTD, YoY %, weighted average, a flagged status) suitable for a macro-economic or portfolio dashboard.
8. Pull a single measure into a specific worksheet cell with CUBEVALUE (and name members with CUBEMEMBER), and generate CUBE formulas automatically by converting a PivotTable with OLAP Tools ▸ Convert to Formulas.
9. Diagnose and prevent the five classic time-intelligence failures, no marked Date table, a non-contiguous or year/month-only "date," SUMX-vs-SUM row-context confusion, forgetting a VAR is frozen at declaration, and auto date/time hierarchies bloating the model.

Prerequisites & connections

Builds on. EX5.01 (Power Pivot & DAX I) is the hard prerequisite: you must already know that a measure is re-evaluated inside filter context, that CALCULATE(expression, filters…) is the one function that modifies that context, that DIVIDE(numerator, denominator, [alternate]) is the safe division that returns blank (or your alternate) instead of #DIV/0!, and that a star schema connects a fact table (Sales) to dimension tables (Product, Date, Customer) by single-direction relationships. Every measure in this module assumes the Total Sales base measure you built there:

`` Total Sales = SUM( Sales[Amount] ) ``

You also need EX4.01–EX4.02 (Power Query) because a real Date table is usually loaded and shaped in Power Query before it lands in the model, and EX1.01's date-math intuition (EOMONTH, fiscal-year thinking) transfers directly to the year_end_date argument here.

Feeds forward. This is the engine room for the dashboards you will build next. EX3.01–EX3.02 (PivotTables, PivotCharts, dashboard craft) become the canvas on which these measures are displayed; the CUBEVALUE section here is precisely how you break out of the PivotTable grid when a dashboard needs a measure in an arbitrary cell. On the analyst track, this module is the direct machinery behind M7.07's macro dashboard of 18 indicators and the portfolio-tracker capstone in EX7.02, a YoY % measure written once here re-prices every indicator tile there. The SUMX weighted-average pattern reappears whenever you compute a weighted-average cost of capital, a portfolio's blended yield, or a weighted index; and the VAR discipline is the readability standard the professional-modeling module (EX7.01) will hold every measure to.

The Date table and why it's mandatory

First principles. Ask yourself what SAMEPERIODLASTYEAR must physically do. The report cell you are in carries a filter context, say, "March 2024." The function's job is to return a different set of dates: "March 2023." To do that it must know what March 2023 even is, which calendar days it contains, that they form a contiguous block, that they sit exactly one year before the current filter. A loose Sales[OrderDate] column buried in your fact table cannot answer that, because it only contains the days on which sales happened: if you sold nothing on 7 March, that day is simply missing, and a function trying to "shift the whole period back one year" now has holes in both the source and the target period. Time intelligence needs a spine of dates that is complete and unbroken, one row for every calendar day in the range, whether or not anything happened on it.