DAX II: CALCULATE and Time

Why this matters

The VP is building a board slide and asks for three numbers: each category's share of company revenue, growth versus last year, and performance against finance's targets. Look at what all three have in common: each one compares a filtered number to a differently-filtered number. Share-of-total needs Furniture's revenue and everyone's revenue, in the same breath. Growth needs this year and last year. Attainment needs actuals and targets. Your measures so far compute one thing in whatever filter context the visual hands them — they cannot look sideways at a different context. The function that can is CALCULATE, and it is the single most important function in DAX.

Filter context, said precisely

Last lesson previewed it; now the full statement. Every cell of every visual evaluates its measure inside a filter context: the combined set of filters coming from the cell's row and column headers, the visual's own filters, any slicers, and any page or report filters. The Furniture row of your margin table computes [Total Revenue] with the model filtered to Category = "Furniture" — 30,481 sales rows survive, and the measure sums those. Change any filter, the context changes, the measure recomputes. This is why one formula showed five margins: five cells, five contexts.

Read that again as a machine, because it is one: filters select rows → measure computes over survivors. Everything in this lesson is about deliberately editing step one.

CALCULATE: the context editor

CALCULATE evaluates a measure in a modified filter context. Its first argument is the measure; every argument after that edits the filters. The canonical first use — percent of total:

Revenue All Categories = CALCULATE ( [Total Revenue], ALL ( Products ) )

% of Total Revenue = DIVIDE ( [Total Revenue], [Revenue All Categories] )

ALL(Products) removes any filter coming from the products table. So in the Furniture cell, [Total Revenue] sees Furniture's context ($24.80M) while [Revenue All Categories] sees the same context with the category filter deleted — the full $66.81M. Divide, and the Furniture row honestly reads 37.1%. Put the measure in the table and the column verifies itself: 37.1 + 27.2 + 15.5 + 12.7 + 7.5 sums to 100.

The date table — and why auto date/time is off

Time questions — YTD, year-over-year — need DAX's time-intelligence functions, and those functions have a non-negotiable requirement: a proper date table. Power BI's default "auto date/time" secretly builds a hidden calendar per date column; it bloats models, cannot be shared across facts, and blocks the professional patterns. Professionals turn it off (File → Options → Data Load) and build one calendar the whole star shares — which is also, not coincidentally, what the targets table has been waiting for since Lesson 4.

New table (a calculated table — DAX that produces rows):

Dates = CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) )

Add helper columns (Year = YEAR(Dates[Date]), Month, Month Number), sort Month by Month Number, then — the step everyone forgets — right-click the table and Mark as date table, confirming the Date column. Marking is what licenses the time-intelligence functions to trust this table. Finally, relate Dates[Date] to Sales[OrderDate] (many-to-one, dates side unique by construction: one row per day, 1,461 rows). The star now has a time spine.

Time intelligence: two functions carry the load

Year-to-date. With the date table marked and related:

Revenue YTD = TOTALYTD ( [Total Revenue], Dates[Date] )

In a monthly table for 2025, the June row reads $9,916,580.47 — January through June accumulated. Every month's YTD is that month plus everything before it in the year; the measure re-derives the accumulation from filter context alone.

Year-over-year. SAMEPERIODLASTYEAR shifts the date filter back one year inside a CALCULATE — literally the context-editing pattern again:

Revenue LY  = CALCULATE ( [Total Revenue], SAMEPERIODLASTYEAR ( Dates[Date] ) )
YoY Growth % = DIVIDE ( [Total Revenue] - [Revenue LY], [Revenue LY] )

Filtered to 2025, Revenue LY reports 2024's $18,444,998.18 and YoY Growth reads +12.0%. Filtered to June 2026, the same two measures report June 2025 and +15.9% — the definitions never change, only the context. This is the whole promise of the measure family: write the comparison once, and every year, month, category, and region inherits it.

The payoff: targets join the star

Finance's targets table (1,080 rows: region × category × month) has sat unrelated since Lesson 4 — the "island" the inherited-model review diagnosed. It needed a spine of unique keys, and now the model has one. Relate Targets[TargetMonth] to Dates[Date] (target months are month-start dates — many target rows to one date row, clean). For region and category, the star needs the small conformed dimensions that review predicted: two tiny calculated tables —

Regions    = DISTINCT ( Stores[Region] )
Categories = DISTINCT ( Products[Category] )

— each related to both its facts (Regions → stores… more precisely: Regions filters targets directly and reaches sales through stores; Categories likewise through products). With the wiring done, attainment is one honest measure:

Target Revenue = SUM ( Targets[RevenueTarget] )
Attainment %   = DIVIDE ( [Total Revenue], [Target Revenue] )

For the first half of 2026: actuals $11,515,008.15 against targets of $10,722,574.96 — 107.4% attainment. Slice by region and the board slide writes itself: the West leads at 112.7%, the Southwest trails at 105.0% — and notice the honest headline hiding there: every region beat its target. Three lessons of modeling and two of DAX, and finance's island is now a column on a chart.

Common mistakes

• Forgetting Mark as date table. The calendar exists, the relationship exists, and time intelligence quietly misbehaves anyway. Marking is the license.
• Leaving auto date/time on alongside your calendar. Two competing time systems; hierarchies that point at hidden tables. Turn it off, on purpose, once.
• ALL on the whole model when you meant one table. ALL(Products) removes category filters; ALL over everything removes the reader's year and region selections too — a percent-of-total that ignores the slicers it shares a page with.
• Comparing YoY across unequal periods. June 2026 against June 2025 is honest; 2026-to-date against all of 2025 is not. SAMEPERIODLASTYEAR shifts the current filter — make sure the current filter is a comparable window.
• Relating targets straight to stores or products. Targets' Region and Category columns are not unique on either side — that is the many-to-many trap again. The conformed dimensions (Regions, Categories) are the one-side both facts share.

Summary