DAX II & Time Intelligence

Excel Mastery Track · Brown belt · ~6–8 hours · Microsoft 365 on Windows assumed

Almost every question a business actually asks about its numbers is a question about time. Not "what were sales?" but "what were sales this year versus last?" Not "what is revenue?" but "what is revenue so far this year, and is that ahead of plan?" Growth, momentum, seasonality, run-rate, cumulative-to-date, these are the words executives speak, and every one of them is a comparison of the same measure across two slices of the calendar. When you first met DAX, you learned the single idea that powers the Data Model: a measure is an expression that is re-evaluated inside whatever filter context the report cell hands it, and CALCULATE is the one function that can change that filter context. This module is what happens when the filter you change is the date filter, when CALCULATE reaches back exactly one year, or forward one quarter, or expands to "everything since January 1st." That is time intelligence, and it is the reason Power Pivot exists.

The whole discipline rests on one unglamorous object that beginners skip and professionals build first: a marked Date table. Time intelligence functions do not read a date the way you or I do; they need a table that contains one row per day, with no gaps, flagged so the engine knows it is the calendar. Give them that and a single short measure computes a year-over-year growth rate that updates for every product, region, and month in your report at once. Withhold it and the same functions return blanks, errors, or (worse) confident wrong answers. So we begin there, with the Date table and why it is mandatory, and only then build the measures every macro dashboard and portfolio tracker is made of: running totals with TOTALYTD, prior-period comparisons with SAMEPERIODLASTYEAR and DATEADD, the year-over-year and year-over-year-% pair, the iterators SUMX and AVERAGEX that compute row by row before they aggregate, the VAR keyword that makes a long measure readable and faster, and finally CUBEVALUE, which lets you pull one measure out of the model and drop it into any cell of a hand-built dashboard grid.

One version note, stated once: everything here assumes Microsoft 365 on Windows with the Power Pivot add-in enabled (File ▸ Options ▸ Add-ins ▸ COM Add-ins ▸ Go ▸ tick Microsoft Power Pivot for Excel). The DAX language and every function below are identical to the DAX you would write in Power BI, so this module doubles as a Power BI foundation, but the UI paths, the CUBE functions, and the "into the grid" workflow are Excel's own.

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, so you must already know a few things. A measure is re-evaluated inside filter context, and CALCULATE(expression, filters…) is the one function that modifies that context. DIVIDE(numerator, denominator, [alternate]) is the safe division that returns blank, or your alternate, instead of #DIV/0!. And a star schema connects a fact table (Sales) to dimension tables (Product, Date, Customer) by single-direction relationships. Every measure here 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 reaches the model. The date-math intuition from EX1.01 (EOMONTH, fiscal-year thinking) also carries straight over to the year_end_date argument here.

Feeds forward. This is the engine room for the dashboards you'll build next. EX3.01–EX3.02 (PivotTables, PivotCharts, dashboard craft) give you the canvas these measures are shown on. When a dashboard needs a measure in an arbitrary cell, CUBEVALUE is how you break out of the PivotTable grid. On the analyst track, what you learn here is the direct machinery behind M7.07's macro dashboard of 18 indicators and the portfolio-tracker capstone in EX7.02. Write a YoY % measure once here and it re-prices every indicator tile there. The SUMX weighted-average pattern comes back whenever you compute a weighted-average cost of capital, a portfolio's blended yield, or a weighted index. And the VAR habit is the readability standard the professional-modelling module (EX7.01) will hold every measure to.

Core teaching content

Iterators: SUMX and AVERAGEX

Everything so far aggregated a column that already existed (SUM(Sales[Amount])). Often, though, the number you want isn't a column at all. It has to be computed row by row first and only then aggregated. That's the job of an iterator. SUMX(table, expression) walks the table one row at a time and evaluates expression in the row context of that row, so it can see every column of that row. Then it sums the results.

The canonical case is a line-extended revenue where the table stores Qty and Price separately but never their product:

Total Revenue = SUMX( Sales, Sales[Qty] * Sales[Price] )

For each row it multiplies that row's quantity by that row's price, then adds the lot up. Contrast the tempting wrong formula:

Wrong Revenue = SUM( Sales[Qty] ) * SUM( Sales[Price] )

That multiplies total quantity by total price, which gives a number with no meaning because it double-counts across rows. The difference is row context. SUMX multiplies within each row before summing, while SUM(...) * SUM(...) sums each column across all rows and only then multiplies. On any table with more than one row they disagree, and Worked example 2 shows how far apart they land.

AVERAGEX(table, expression) is the same idea with an average at the end. It evaluates the expression per row, then averages across rows. It's how you compute an average line value, an average revenue per store, or an average order size:

Avg Line Value = AVERAGEX( Sales, Sales[Qty] * Sales[Price] )

The iterator family (SUMX, AVERAGEX, MINX, MAXX, COUNTX, RANKX) is the workhorse behind every "per-row-then-aggregate" calculation. Its most important use is the weighted average, which is a SUMX over the numerator divided by a SUM of the weights:

Weighted Price = DIVIDE( SUMX( Sales, Sales[Qty] * Sales[Price] ), SUM( Sales[Qty] ) )

That gives you a weighted-average price, with each price weighted by its quantity. It's the same shape as the weighted-average cost of a stock position, a blended coupon on a bond portfolio, or a volume-weighted average price (VWAP). Worked example 2 checks it on a three-lot US position.

VAR for readable measures

Look again at the YoY % measure. The prior-year CALCULATE appears twice. Long measures pile up repetitions like this until they're unreadable and slow, and the VAR/RETURN construct is the cure. You declare named variables, then a single RETURN expression uses them:

Sales YoY % =
VAR CurrentSales  = [Total Sales]
VAR PriorSales    = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
VAR Yoy           = CurrentSales - PriorSales
RETURN
    DIVIDE( Yoy, PriorSales )

You get the same result as the earlier version, and three things improve at once. It's easier to read, because each step has a name and a colleague can follow the logic top to bottom like prose. It's faster, because PriorSales is computed once and reused. The old version computed it for the numerator and again for the denominator. And it's easier to debug, because you can temporarily RETURN PriorSales to see an intermediate value, then switch back.

There's one rule you have to internalise, because breaking it produces baffling bugs. A VAR is evaluated once, at the moment it is declared, in the filter context where it is declared. After that it is a frozen constant. It doesn't re-evaluate later when it's used inside a CALCULATE that changes the context. So this won't give you the prior year:

Broken =
VAR Prior = [Total Sales]                          -- frozen in the CURRENT context
RETURN
    CALCULATE( Prior, SAMEPERIODLASTYEAR('Date'[Date]) )  -- context change ignored; Prior is a constant

Prior captured this year's number the instant it was declared. Wrapping it in CALCULATE afterwards changes nothing, because Prior is now just a value and there's nothing left to re-evaluate. To shift context you have to put the measure or expression itself inside the CALCULATE. A pre-captured variable won't do. It's an easy bug to write, because the broken measure still returns a believable number. Capture the value you want after any context change, never before. That one rule separates people who use VAR decoratively from people who use it correctly.

CUBEVALUE into the grid

PivotTables are fast to build but rigid. Their rows and columns must nest, and you can't easily put "Total Sales for 2024" in cell C7, "YoY %" in C8, and a sparkline beside them, all with your own labels and formatting. For a hand-crafted dashboard you want to pull individual measure values out of the model and place each one in a cell of your choosing. The CUBE functions do that.

CUBEVALUE fetches one aggregated value from the Data Model:

=CUBEVALUE( "ThisWorkbookDataModel",
            "[Measures].[Total Sales]",
            "[Date].[Year].&[2024]" )

Its first argument is the connection name. For a workbook's own model it's always the literal text "ThisWorkbookDataModel". The remaining arguments are member expressions. Here "[Measures].[Total Sales]" names the measure, and "[Date].[Year].&[2024]" slices it to the year 2024. You can add more member arguments to slice further ("[Product].[Category].&[Bonds]"). Better still, point a member argument at a slicer or cell reference so the value recalculates as the user clicks. Replace the hard-coded year with a reference to a slicer's CUBEMEMBER cell and the whole grid becomes interactive.

CUBEMEMBER names a member (a year, a product, a measure) and is what slicers and cell references resolve to:

=CUBEMEMBER( "ThisWorkbookDataModel", "[Date].[Year].&[2024]" )

You'll rarely type CUBE formulas from scratch, because there's a fast way to generate them. Build the PivotTable you want, then go to PivotTable Analyze ▸ OLAP Tools ▸ Convert to Formulas. Excel replaces the entire pivot with a grid of CUBEMEMBER and CUBEVALUE formulas in the same cells. You're now free to move, relabel and reformat them like any other cell, and they stay live-connected to the model. It's the professional's route from "a PivotTable that proves the numbers" to "a bespoke dashboard that looks designed." The analyst track's macro-indicator dashboard uses this route to lay 18 measures into a fixed, branded grid.

Common mistakes & how experts think differently

1. Doing time intelligence without a marked, contiguous Date table. Of all the mistakes on this list, this is the signature one. Beginners point TOTALYTD or SAMEPERIODLASTYEAR at the OrderDate column sitting in the fact table and get blanks, half-right staircases, or totals that never reset. The cause is always the same. Either there's no dedicated Date table, or there's one that was never Mark as Date Table-flagged, or there's one with gaps because it only holds the days on which sales happened. The expert's reflex is to build the calendar first, before writing a single measure. Generate it in Power Pivot or load a Power Query calendar. Confirm it runs one contiguous row per day across the full range. Then mark it and relate it. You can't bolt time intelligence on afterwards, because the Date table is what makes it possible.
2. Feeding time functions a Year/Month pair instead of a real date column. SAMEPERIODLASTYEAR, DATEADD, and DATESYTD need an actual date-typed, unique, contiguous column, because they compute "one year earlier" by arithmetic on real dates. Give them a [Year] integer and a [Month] name and they've got nothing to shift, so the measure returns blank or errors. The expert stores the true Date key for the engine. The Year/Quarter/Month columns are there purely for display and sorting on the report, and they never go in as the argument to a time function.
3. Confusing SUMX (row context) with SUM (column aggregation). Writing SUM(Sales[Qty]) * SUM(Sales[Price]) when you meant SUMX(Sales, Sales[Qty]*Sales[Price]) produces a number that looks plausible and is badly wrong. It multiplies column totals instead of multiplying within each row first. The expert asks, "does the number I want exist as a column, or must it be computed per row?" If it's per row, the answer is an iterator (…X) every time. Watch for any expression that multiplies or divides two columns. That almost always belongs inside a SUMX/AVERAGEX, and almost never between two SUMs.
4. Forgetting a VAR is evaluated once and frozen at declaration. People declare VAR x = [Total Sales] and then wrap x in a CALCULATE(..., SAMEPERIODLASTYEAR(...)), expecting the prior year. But x captured the current context the moment it was declared, and it's now an immovable constant, so the context change is ignored. The expert captures a value only after any context shift. To get the prior year, you put the measure itself inside the CALCULATE, never a pre-captured variable. A variable holds a result, and a result can't be re-evaluated.
5. Letting auto date/time hierarchies (and automatic PivotTable date grouping) bloat and confuse the model. Excel will happily auto-group any date field you drop into a PivotTable into Year/Quarter/Month. Power BI's "auto date/time" creates a hidden mini-calendar behind every date column. Both inflate the model, fragment your time logic across invisible tables, and collide with the real Date table you built. The expert turns the automatic grouping off (File ▸ Options ▸ Data ▸ Disable automatic grouping of Date/Time columns in PivotTables). They keep one marked Date table as the single source of calendar truth and route every time-intelligence measure through it. Aim for one marked, related calendar that you own, and never a scatter of implicit ones.
6. Using the / operator instead of DIVIDE in a YoY %. The first year in any model has no prior year, so the denominator is blank. Then / throws #DIV/0! or produces an infinity that poisons the visual's axis. DIVIDE(num, den) returns blank, or a stated alternate, on a zero/blank denominator. The expert reaches for DIVIDE on every ratio measure by habit, even when a zero seems unlikely.
7. Assuming PARALLELPERIOD behaves like SAMEPERIODLASTYEAR. They look interchangeable until a month-level cell shows that PARALLELPERIOD(…, -1, YEAR) returned the whole prior year when you expected the same month last year. The expert keeps the distinction crisp. SAMEPERIODLASTYEAR/DATEADD preserve the period's shape, while PARALLELPERIOD snaps to the entire shifted interval. Pick between them on purpose.

One habit ties these together. An expert builds the calendar before the measures and thinks in filter context. They treat every ratio and every per-row calc as a deliberate choice of tool: iterator or aggregator, shape-preserving or whole-interval, DIVIDE or nothing. Beginners write the measure they hope works. Professionals write the one they can prove does.

Worked examples

Worked example 1: India monthly YoY %: SAMEPERIODLASTYEAR over a two-year series (India, ₹)

The task. An Indian consumer-goods distributor has monthly sales for calendar 2023 and 2024 in the model. Build the [Sales YoY %] measure and check the year-over-year growth for March 2024 by hand. Then read the full twelve-month comparison that one measure produces for every row at once.

Step 1. The base measure and the data. The base measure is the one you already have, Total Sales = SUM(Sales[Amount]). Here are the monthly [Total Sales] values the model returns, in rupees, with the marked Date table supplying the calendar:

Step 2. The measure. The prior-year value comes from CALCULATE re-running [Total Sales] under the SAMEPERIODLASTYEAR date shift. The growth rate divides the change by that prior-year value, with DIVIDE as the guard:

Sales YoY % =
DIVIDE(
    [Total Sales] - CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) ),
    CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
)

Step 3. Verify March 2024 by hand. Take the "March 2024" cell. Its filter context is the 31 days of March 2024, and [Total Sales] there is ₹50,40,000. SAMEPERIODLASTYEAR shifts that context to the 31 days of March 2023, so CALCULATE([Total Sales], SAMEPERIODLASTYEAR(...)) returns ₹42,00,000. Then:

• Numerator (the change): 50,40,000 − 42,00,000 = ₹8,40,000.
• YoY %: 8,40,000 ÷ 42,00,000 = 0.20 = 20.00%.

Recomputed in Python, every figure matched. March 2024 YoY % = 20.00%.

Step 4. Read what one measure bought you. That single measure fills the whole YoY % column above with no per-row formulas. March and September are up 20%, July and August a softer 5%, and everything else 10%. It would do the same broken out by product or region, because it obeys filter context. Now look at the year-total row, where the same measure produces the annual roll-up. Total 2023 = ₹5,48,00,000 and total 2024 = ₹6,06,80,000, a change of ₹58,80,000, so annual YoY % = 10.73% (58,80,000 ÷ 5,48,00,000). Notice that the month figures don't "average" to the annual figure. The annual rate is weighted by each month's size, so you compute it from the totals and never by averaging the monthly percentages.

Here's a real-world wrinkle that's outside the core lesson. An Indian distributor reports on a 1 April–31 March fiscal year, so a fiscal YoY would compare FY2024-25 (Apr 2024–Mar 2025) against FY2023-24. The measure doesn't change. Only the Date table's fiscal columns and the report's grouping do. The mechanics you verified above stay identical, and the calendar is the only thing you swap.

Worked example 2: US position weighted cost and YTD revenue: SUMX + TOTALYTD (US, $)

The task. A US desk holds one equity position built from three buy lots. It wants (a) the weighted-average cost per share via a SUMX measure, and (b) the year-to-date revenue at the end of May from a monthly sales series via TOTALYTD. Verify both by hand.

Step 1. The weighted-average cost. The Sales table, called Lots here, stores each lot's Qty and Price but not their product. The weighted price is total cost divided by total shares:

Weighted Price = DIVIDE( SUMX( Sales, Sales[Qty] * Sales[Price] ), SUM( Sales[Qty] ) )

The three lots and the per-row products SUMX computes:

SUMX(Sales, Sales[Qty]*Sales[Price]) walks the three rows and multiplies within each one. Adding those up gives 5,000 + 9,300 + 14,250 = $28,550.00. SUM(Sales[Qty]) = 500. So:

• Weighted Price = 28,550.00 ÷ 500 = $57.10 per share.

The tempting wrong answer is the simple average of the three prices, (50 + 62 + 57) ÷ 3 = $56.33. That ignores that the $57 lot is 250 shares and the $50 lot only 100. The weighting pulls the true cost above the simple mean. The two differ by $0.77 a share, and on 500 shares that's a $385 misstatement of cost basis. So a weighted average is a job for SUMX, and AVERAGE can't do it.

Step 2. The YTD revenue. Here are the monthly [Total Sales] for 2024, in dollars, and the running [YTD Sales] = TOTALYTD([Total Sales], 'Date'[Date]):

Step 3. Verify May by hand. TOTALYTD at the "May 2024" cell sums [Total Sales] over 1 January–31 May 2024:

• 120,000 + 135,000 + 150,000 + 145,000 + 160,000 = $710,000.

Recomputed in Python, YTD at May = $710,000. The December YTD equals the full-year total, $1,905,000, as it must, because the running total at year-end is the annual sum. Both check out.

Step 4. Read it. One SUMX measure gives a weighted cost that re-blends by itself if a lot is added or a price corrected. One TOTALYTD measure gives a running revenue that resets each January and re-slices by any dimension. Neither number is typed anywhere. Both are computed from the model, which is what DAX promises: change a lot or a month and every dependent figure re-derives itself. Swap TOTALYTD([Total Sales], 'Date'[Date]) for TOTALYTD([Total Sales], 'Date'[Date], "1/31") and the same measure now runs a US-retail fiscal year ending 31 January. None of the machinery changes.

Practice set

Work each problem before you read its solution. Every measure assumes the base measure Total Sales = SUM(Sales[Amount]) and a marked, contiguous 'Date' table related to Sales. The DAX is given in full.

P1 (guided). Write the YTD measure. Write a [YTD Sales] measure that gives the calendar year-to-date of [Total Sales], then state the one-argument change that switches it to an Indian fiscal year ending 31 March.

Solution.

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

For the Indian fiscal year, add the year_end_date argument "3/31":

Fiscal YTD Sales = TOTALYTD( [Total Sales], 'Date'[Date], "3/31" )

The calendar version resets every 1 January. The fiscal version resets every 1 April, so its June value sums April+May+June where the calendar one sums January–June. Otherwise the measure body is identical, and only the reset date moves.

P2 (guided). Prior-year value two ways. Write a [Sales LY] measure returning the prior-year value of [Total Sales], once with SAMEPERIODLASTYEAR and once with DATEADD. Are they equal?

Solution.

Sales LY          = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
Sales LY (DATEADD) = CALCULATE( [Total Sales], DATEADD( 'Date'[Date], -1, YEAR ) )

Yes. They return the identical value for any clean calendar period, because both shift the date filter back one year while preserving the period's shape. Prefer SAMEPERIODLASTYEAR for the year case, since the name documents intent. Reach for DATEADD when you need a non-year shift such as -1, MONTH or -1, QUARTER, which SAMEPERIODLASTYEAR can't express.

P3 (guided). YoY % with DIVIDE. Write the [Sales YoY %] measure, and explain why DIVIDE is used instead of /.

Solution.

Sales YoY % =
DIVIDE(
    [Total Sales] - CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) ),
    CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
)

DIVIDE returns blank, or a stated alternate, when the denominator is blank or zero. That happens in the model's first year, where there's no prior period. The / operator would throw #DIV/0! or an infinity that distorts the chart axis. Make DIVIDE your habit for every ratio measure.

P4 (numeric). Verify a YoY %. Using the India series from the first worked example, [Total Sales] for October 2023 = ₹52,00,000 and October 2024 = ₹57,20,000. Compute [Sales YoY %] for October 2024, in percent.

Solution. SAMEPERIODLASTYEAR shifts the context to Oct 2023, so the prior year = ₹52,00,000. The change = 57,20,000 − 52,00,000 = ₹5,20,000, and YoY % = 5,20,000 ÷ 52,00,000 = 0.10 = 10.00%. That's verified in Python. The measure gives this for the October cell without any per-row formula.

P5 (numeric). Verify a weighted price. A position has two lots, 200 shares @ $45.00 and 300 shares @ $50.00. Using Weighted Price = DIVIDE(SUMX(Lots, Lots[Qty]*Lots[Price]), SUM(Lots[Qty])), compute the weighted-average cost per share.

Solution. Row by row, SUMX computes 200 × 45 = 9,000 and 300 × 50 = 15,000, which sum to $24,000. SUM(Qty) = 500. Weighted Price = 24,000 ÷ 500 = $48.00, verified in Python. The simple average of the two prices, (45+50)/2 = $47.50, is wrong because it ignores that the $50 lot is larger. Weighting pulls the cost up to $48.00.

P6 (numeric). SUMX vs the wrong product. A four-line order has line quantities and prices: 100 @ $12.50, 250 @ $8.50, 40 @ $21.25, 300 @ $5.75. Compute Total Revenue = SUMX(Order, Order[Qty]*Order[Price]), and also compute the average line value with AVERAGEX.

Solution. The per-row extended values are 1,250.00, 2,125.00, 850.00 and 1,725.00. SUMX sums them to get 1,250 + 2,125 + 850 + 1,725 = $5,950.00. AVERAGEX averages the four, giving 5,950 ÷ 4 = $1,487.50. Both are verified in Python. Compare the wrong formula, SUM(Qty)*SUM(Price) = 690 × 48.00 = $33,120. That number means nothing, because it multiplies column totals instead of multiplying within each row first.

P7. Month-over-month. Write a measure for the previous month's [Total Sales], then a [Sales MoM %]. Why can't SAMEPERIODLASTYEAR do this?

Solution.

Sales PM =
CALCULATE( [Total Sales], DATEADD( 'Date'[Date], -1, MONTH ) )

Sales MoM % =
VAR Cur   = [Total Sales]
VAR Prev  = CALCULATE( [Total Sales], DATEADD( 'Date'[Date], -1, MONTH ) )
RETURN DIVIDE( Cur - Prev, Prev )

SAMEPERIODLASTYEAR shifts by one year and nothing else. A month shift needs the general DATEADD with -1, MONTH, and DATEADD is the tool whenever the interval isn't a year.

P8. Refactor with VAR. Rewrite the YoY % measure from P3 using VAR/RETURN so the prior-year value is computed once. State the one rule about when a VAR is evaluated.

Solution.

Sales YoY % =
VAR PriorSales = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
VAR Yoy        = [Total Sales] - PriorSales
RETURN
    DIVIDE( Yoy, PriorSales )

PriorSales is now computed once and reused, so the measure is faster and can't drift between two edited copies. The rule says that a VAR is evaluated once, in the filter context where it is declared, and is then a frozen constant. It doesn't re-evaluate if it's later wrapped in a CALCULATE that changes context.

P9. The frozen-VAR trap. A colleague writes the measure below hoping to get last year's sales, but it returns this year's. Explain the bug and fix it.

Attempt =
VAR Prior = [Total Sales]
RETURN CALCULATE( Prior, SAMEPERIODLASTYEAR( 'Date'[Date] ) )

Solution. Prior was evaluated the instant it was declared, in the current filter context, so it captured this year's [Total Sales] as a constant. Wrapping that constant in CALCULATE(..., SAMEPERIODLASTYEAR(...)) changes nothing, because a value can't be re-evaluated. To fix it, put the measure itself inside the CALCULATE in place of the pre-captured variable:

Fixed = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )

Capture values after any context change, never before.

P10. PARALLELPERIOD vs SAMEPERIODLASTYEAR. At a "March 2024" cell, contrast what CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) returns against CALCULATE([Total Sales], PARALLELPERIOD('Date'[Date], -1, YEAR)). Use the numbers from the first worked example, where Mar 2023 = ₹42,00,000 and full-year 2023 = ₹5,48,00,000.

Solution. SAMEPERIODLASTYEAR preserves the period's shape, so it returns March 2023 = ₹42,00,000. PARALLELPERIOD(…, -1, YEAR) snaps to the whole prior interval and returns all of 2023 = ₹5,48,00,000, whatever the month in context. Use PARALLELPERIOD on purpose for "this month vs the entire prior year" comparisons. Expecting same-month behaviour from it is a classic trap.

P11. CUBEVALUE into a cell. Write the worksheet formula that pulls [Total Sales] for the year 2024 from the workbook's Data Model into a single cell, and name the fastest way to generate a whole grid of such formulas.

Solution.

=CUBEVALUE( "ThisWorkbookDataModel", "[Measures].[Total Sales]", "[Date].[Year].&[2024]" )

The first argument is the workbook model's fixed connection name, "ThisWorkbookDataModel". The second names the measure, and the third slices to 2024. With the first worked example's totals, this returns ₹6,06,80,000. The fastest way to generate many is PivotTable Analyze ▸ OLAP Tools ▸ Convert to Formulas, which turns an entire PivotTable into live CUBEMEMBER/CUBEVALUE formulas you can then move and reformat freely.

P12 (timed: 6 min). Choose the right tool. For each mini-task, name the best DAX function or idiom and justify it in one line. (a) Running revenue from 1 April in an Indian fiscal year. (b) Same month last year. (c) Previous quarter. (d) A weighted-average yield across bond lots. (e) Put one measure value in cell G4 of a hand-built dashboard.

Solution. (a) TOTALYTD([Total Sales], 'Date'[Date], "3/31"), which is YTD with the fiscal year_end_date. (b) SAMEPERIODLASTYEAR('Date'[Date]) inside CALCULATE, the one-year, shape-preserving shift. (c) DATEADD('Date'[Date], -1, QUARTER) inside CALCULATE, the general shift for a non-year interval. (d) DIVIDE(SUMX(Lots, Lots[Qty]*Lots[Yield]), SUM(Lots[Qty])), a SUMX weighted average. (e) CUBEVALUE("ThisWorkbookDataModel", …), which pulls one measure into an arbitrary cell. The meta-skill is to settle four things before you write any DAX. Decide whether it's running or point-in-time. Decide whether the interval is a year or something else. Then decide whether it's per-row or a column aggregate, and whether it goes in a pivot or a free cell.

Applied mini-project

Build a time-intelligence KPI dashboard.

Assemble a small but complete dashboard that turns a two-year Sales fact table into a set of live time-intelligence KPIs. Every number should be a measure, nothing should be typed twice, and the whole thing should re-slice by year as well as by product and region.

Set up the model (Power Pivot ▸ Manage):

1. Sales (fact), OrderDate, ProductID, Region, Qty, Price, Amount (or Amount = Qty × Price as a calculated column). At least two full calendar years of monthly rows.
2. Date (dimension). Generate it with Design ▸ Date Table ▸ New, or load a Power Query calendar with Year, Quarter, Month, MonthNo, FiscalYear columns. Then use Design ▸ Mark as Date Table on the Date column, and confirm it's contiguous, with one row per day and no gaps.
3. Product and Region dimensions, related to Sales in Diagram View as a star schema.

Author the measures (each in the measure dialog, with a number format):

1. Total Sales = SUM(Sales[Amount]), the base.
2. YTD Sales = TOTALYTD([Total Sales], 'Date'[Date]), and a Fiscal YTD Sales variant with "3/31".
3. Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])).
4. Sales YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY]), then refactored with VAR for readability.
5. Weighted Price = DIVIDE(SUMX(Sales, Sales[Qty]*Sales[Price]), SUM(Sales[Qty])).
6. Avg Monthly Sales = AVERAGEX(VALUES('Date'[MonthNo]), [Total Sales]), an iterator over months.

Build the dashboard:

1. A PivotTable proof sheet, with the Date hierarchy on rows and [Total Sales], [YTD Sales], [Sales LY], [Sales YoY %] on values. Confirm the YTD staircase resets and the YoY % blanks out in the first year, where there's no prior period. That's your proof the DIVIDE guard works.
2. Convert a copy to CUBE formulas with PivotTable Analyze ▸ OLAP Tools ▸ Convert to Formulas. Then relayout the CUBEVALUE/CUBEMEMBER cells into a branded KPI grid, with a tile each for latest-month sales, YTD, YoY %, and weighted price. Wire it to a Year slicer so the whole grid recalculates on click.
3. Do a robustness pass. Add a month with zero sales and confirm the Date table's contiguity keeps the YTD and YoY correct, because the missing-activity day is still a calendar row. Then point a measure at the raw Sales[OrderDate] instead of 'Date'[Date] and watch time intelligence break. Restore it, and note why the marked Date table was the fix.

Rubric (self-score, where 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its Date table and measure set become the time-intelligence spine you'll reuse for the macro-indicator dashboard and the portfolio tracker in the capstones.

Lab: open the DAX time intelligence workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function references (reach for these first):

• Microsoft Learn, the official DAX function reference pages for TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, SUMX, AVERAGEX, and the VAR keyword, with authoritative argument definitions and context notes. [Free] [Beginner]
• Microsoft Learn. "Understand functions for parent-child hierarchies" is skippable, but "Create a date table in Power Pivot" and "Mark as Date Table" are essential. They're the definitive UI walkthrough for the object everything here rests on. [Free] [Beginner]

Deeper practice and the definitive treatment:

• SQLBI (Marco Russo & Alberto Ferrari). The Definitive Guide to DAX and the free sqlbi.com articles on time intelligence, CALCULATE, and VAR are the gold standard. Their "Time intelligence in DAX" and "Using variables in DAX" pieces are the obvious next reading. [Book paid / articles Free] [Intermediate]
• DAX Guide (dax.guide, by SQLBI), a live, searchable reference for every function with argument diagrams and examples. Keep it open while you write measures. [Free] [Beginner]

Fiscal-calendar and modelling craft:

• Any reputable "build a Power Query date table" tutorial that adds fiscal-year columns for a 1 April Indian year-end. Internalise that the measure never changes, and only the calendar does. [Free] [Intermediate]
• SQLBI, "Automatic time intelligence in Power BI", on why to turn auto date/time off. It's the full argument behind common-mistake #5. [Free] [Intermediate]

Bridge forward. The CUBE-formula workflow here connects straight to EX3.02's dashboard craft and to the analyst track's macro dashboard. Once you can pull any measure into any cell, the layout becomes a design problem and stops being a data one.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand. That's how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds to the hand method and never replaces it. (R1/R10)

AI-Augment this skill

{
  "skill": "Time intelligence's hidden dependency on a contiguous, marked Date table, and the India fiscal-year (April-March) versus calendar-year split as a real source of silently different, both-legitimate numbers",
  "use": "SAMEPERIODLASTYEAR and TOTALYTD fail in a specific, non-obvious way: not with an error, but with a number that is quietly wrong because the calendar behind it has gaps or the wrong year-end. Use an assistant to draft the CHECK you would otherwise skip — a contiguity assertion on the Date table (every day from min to max present, no gaps) and a fiscal-vs-calendar reconciliation of a YTD figure — rather than to assert that a given date table or a given YTD number is fine.",
  "tools": ["Coding assistants — Claude, ChatGPT, or a notebook's inline assistant to draft the contiguity check and the calendar-vs-fiscal YTD contrast below", "Chat assistants — for explaining why a specific SAMEPERIODLASTYEAR cell returned a smaller number than expected", "Document/PDF AI — for confirming a specific company's actual fiscal year-end from its own annual report before assuming April-March or January-December"],
  "prompt": "Here is my Date table's date range and row count: <PASTE min date, max date, row count>. Compute whether it is truly contiguous (row count should equal the number of calendar days between min and max, inclusive) and tell me exactly which dates are missing if it is not. Separately, given this monthly revenue series <PASTE>, compute both the calendar-year-to-date and the fiscal-year-to-date (year-end 31 March) figures for the same month, and show me both numbers rather than assuming which one I meant.",
  "verify": "Recompute the Date table's expected row count by hand (days between min and max plus one) and compare it to the actual row count yourself before trusting a 'looks contiguous' verdict; for a fiscal-vs-calendar figure, re-derive which specific months each convention sums by hand and confirm the assistant summed the right set, since a one-month slip in either direction is easy to make and easy to miss.",
  "diy": "The gate is unaided: you state from memory the three non-negotiable properties of a Date table, explain why a table built from DISTINCT(Sales[OrderDate]) fails the contiguity requirement, and compute a fiscal-year-to-date figure by hand from monthly data given only the year-end date — with no tool in the room."
}

Modern Data Analysis

By hand first. You can already build a marked, contiguous Date table and write TOTALYTD and SAMEPERIODLASTYEAR. You also know the fiscal year_end_date argument moves the reset date without changing the measure body. Code gives you a way to actually test the Date table's contiguity. That property is easy to state as a rule and easy to break without noticing, because a table missing a handful of days still looks completely normal in the field list.

Today's workflow. In pandas, the analogue of a marked Date table is a DatetimeIndex built with pd.date_range(start, end, freq='D'), which is contiguous by construction. You merge it against a sparse transactions table via reindex, so the gaps become explicit NaN rows instead of absent ones. SAMEPERIODLASTYEAR's pandas equivalent is a groupby on a DateOffset-shifted index. TOTALYTD with a fiscal year-end is a groupby on a custom fiscal-year label derived from the same contiguous index. Building both versions side by side is the fastest way to see which of DAX's guarantees, such as "the calendar is complete," a hand-rolled table assumes without providing.

Tools & sources (IN + US). Use pandas for the contiguity check and the fiscal-vs-calendar YTD contrast below. For India, take the same monthly distributor series the first worked example builds, on a real April–March books-close. Companies Act 2013, Section 2(41), defines a company's "financial year" as ending 31 March. For the US, take the same SaaS monthly series the second worked example builds, on a calendar year-end. Set it against the US federal government's own October–September fiscal year (31 U.S. Code section 1102). It's a reminder that "fiscal year" has no single meaning even within one country.

# recomputed in-session — R3
india_2023 = {"Jan":3800000,"Feb":4000000,"Mar":4200000,"Apr":3900000,"May":4100000,"Jun":4400000,
              "Jul":4600000,"Aug":4800000,"Sep":4500000,"Oct":5200000,"Nov":5800000,"Dec":5500000}
india_2024 = {"Jan":4180000,"Feb":4400000,"Mar":5040000,"Apr":4290000,"May":4510000,"Jun":4840000,
              "Jul":4830000,"Aug":5040000,"Sep":5400000,"Oct":5720000,"Nov":6380000,"Dec":6050000}
fy2023_24 = sum(india_2023[m] for m in ["Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]) + \
            sum(india_2024[m] for m in ["Jan","Feb","Mar"])
cy2023 = sum(india_2023.values())
print(fy2023_24, cy2023, fy2023_24-cy2023)   # 56,420,000  54,800,000  1,620,000

Verify. CY2023 recomputes to ₹5,48,00,000 and the March 2024 [Sales YoY %] recomputes to 20.00%. Both match the first worked example's own figures.

Quantitative lens

A non-contiguous Date table breaks SAMEPERIODLASTYEAR with no error message, and the mistake it causes is large. Common mistake #1 above already states the mechanism. A Date table built as DISTINCT(Sales[OrderDate]), a beginner's substitute for a real calendar, only contains the days on which a transaction happened. To simulate that against the first worked example's March figures, construct daily transaction dates for March 2023 and March 2024. Use 20 of the 31 days each, independently distributed, summing to the example's ₹42,00,000 and ₹50,40,000.

With a real contiguous Date table, SAMEPERIODLASTYEAR shifts the whole 31-day March-2024 block back to the whole 31-day March-2023 block. It recovers the true prior-year total, ₹42,00,000, whichever days happened to carry a sale. With the DISTINCT(OrderDate) substitute, the current period's filter context is only the specific transaction dates in March 2024. SAMEPERIODLASTYEAR shifts each of those calendar dates back one year, and it can only match a 2023 row when the day of the month happens to coincide. Of the 20 shifted dates, only 14 land on an actual 2023 transaction day.

The resulting "prior year" figure is ₹30,68,700 against the true ₹42,00,000, understated by ₹11,31,300, 26.9%. The current-period figure is never shifted, so it stays untouched. The reported [Sales YoY %] therefore becomes 64.24% against the true 20.00%. Nothing else on the report looks broken. Only the YoY column is wrong, and nothing flags it. Worse, it errs toward overstating growth, because the broken table always undercounts the prior year while leaving the current year alone.

India's April–March fiscal year and the calendar year are both legitimate windows over the same data, and they disagree by a real, computable amount. Take the first worked example's monthly series. The fiscal year FY2023-24 (April 2023–March 2024) totals ₹5,64,20,000. The calendar year CY2023 (January–December 2023) totals ₹5,48,00,000. Both are real 12-month windows over the identical monthly figures, and both could fairly be called "2023." They differ by ₹16,20,000, 2.96%, purely because they swap three months, with Jan–Mar 2023 out and Jan–Mar 2024 in.

The mid-year case is sharper. At the same September-2024 snapshot, calendar YTD (January–September, 9 months) totals ₹4,25,30,000, while fiscal YTD on an April year-end (April–September, 6 months, FY2024-25) totals ₹2,89,10,000. The calendar figure is 32.0% larger, and the whole gap comes from spanning three more months. Performance has nothing to do with it. India's Companies Act 2013 mandates the April–March financial year for companies. The US federal government runs an October–September fiscal year by statute (31 U.S.C. section 1102). Most US public companies use a January–December fiscal year, though not all do. Apple, Microsoft, and Walmart are well-known exceptions on their own 10-K cover pages. So getting TOTALYTD's year_end_date argument wrong is more than a formatting slip. It swaps one of these legitimate but different windows for another, and nothing on the report tells you it happened.

A word on limits. The non-contiguous Date-table simulation is a constructed reconstruction of the mechanism the text above already describes in prose. It was built to produce clean, round anchor figures (₹42,00,000 and ₹50,40,000), and it isn't a live capture from a running Power Pivot engine. The specific 14-of-20 day overlap is a property of the particular random construction used here, which is seeded and reproducible. It isn't a general rate at which real transaction calendars coincide year over year. The India Companies Act citation states the statutory financial-year rule for companies. Always confirm a specific company's actual fiscal year against its own annual report, since Section 2(41) permits exceptions for foreign subsidiaries.

Where this goes next: galaxy cross-links

• G2 · DA1.03 pandas II: Cleaning, Time Series & the Excel↔Python Bridge: pd.date_range and reindex are the direct code-native way to build and verify the contiguous calendar your Date table depends on.
• G2 · DA1.05 Statistics in Code: a year-over-year comparison built on a calendar with hidden gaps is a data-integrity problem before it's a statistics problem. That module's habit of checking the data before trusting the number applies directly here.
• G2 · QD2.03 Performance & Attribution Analytics in Code. YoY and YTD measures are the same return-attribution arithmetic that module builds in Python, and they rest on the same contiguous-calendar assumption your Date table exists to satisfy.

Teach it back & journal

Feynman prompt. Explain to a shopkeeper who keeps a paper sales diary how a "year-over-year" number is really computed. Use the image of a diary with one page per day. To know whether March this year beat March last year, you flip back twelve months, add up the same block of days, and compare. And you need a page for every day, even the days you sold nothing, or your flipping back ends up in the wrong place. Then explain the one twist. What if you flip back a year but grab the whole previous year instead of just that month? When would you actually want that, and when would it fool you? Only in your last two sentences may you use the words Date table, SAMEPERIODLASTYEAR, and PARALLELPERIOD. Attach each one to the moment in the diary story it describes.

Journal prompt. Find one report you already produce (a monthly sales sheet, a portfolio statement, a KPI email) and hunt it for two things. Look for (1) any growth or "vs last year" number that's typed or hand-calculated where a SAMEPERIODLASTYEAR measure could compute it live, and (2) any running total you rebuild by hand each month instead of a TOTALYTD. Write down what you found. Then rebuild one KPI as a DAX measure over a proper marked Date table, and note what got easier the moment the calendar lived in one contiguous table instead of being scattered across your formulas. Last, write the single sentence you want to remember forever. It should say why the Date table, far from being optional overhead, is the coordinate system every time comparison secretly stands on. It should also say why you'll never again wire a time function to a loose Year/Month pair.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.