Lecture video · L06 DAX II: CALCULATE & Time

[Slide 1] Tonight is the summit of Module Two. The vice president wants three numbers for a board slide: each category's share of the company, growth against last year, and performance against finance's targets. All three share a secret: each one compares a filtered number to a differently filtered number, and nothing you can currently write can look sideways at a second context. The function that can is called CALCULATE, and it is — no exaggeration — the most important function in DAX. By the end of tonight you will also have built a proper date table, written year-to-date and year-over-year in two lines each, and connected the targets table that has been sitting alone in your model for two weeks. The island becomes a chart tonight.

[Slide 2] First, the concept from last week's trailer, stated precisely. Every cell of every visual evaluates its measure inside a filter context: the combined filters from the cell's row and column headers, the visual's filters, the slicers, and the page. It is a two-step machine: filters select rows, then the measure computes over the survivors. The Furniture row of your margin table filters the model to Furniture — thirty thousand four hundred eighty-one sales rows survive — and Total Revenue sums those, giving twenty-four point eight million. Five cells, five contexts, one formula: that is where last week's five margins came from. Hold the machine in your head, because everything tonight is about deliberately editing step one.

[Slide 3] CALCULATE evaluates a measure in a modified filter context: the first argument is the measure, and everything after it edits the filters. The canonical first use is percent of total. Revenue All Categories wraps Total Revenue in CALCULATE with ALL of Products — and ALL is a filter remover: it deletes whatever filters arrive from the products table. So in the Furniture cell, the plain measure sees Furniture's twenty-four point eight million, while the wrapped one sees the same context with the category filter deleted — the full sixty-six point eight one million. Divide them: Furniture is thirty-seven point one percent of the company. And here is the discipline that keeps CALCULATE honest for the rest of your career: before writing the arguments, say the sentence — compute this measure, as if the filters were… If you cannot finish the sentence in words, you are not ready to write the formula.

[Slide 4] Prediction beat. A student writes the percent-of-total denominator with ALL over the entire sales table instead of ALL of products — and the page has a Year slicer. What breaks? Say the sentence for their denominator: compute revenue as if sales carried no filters at all. No category filter — good, that was the intent. But also no year filter. The reader selects twenty twenty-five and the denominator quietly stays all-time: every percentage shrinks to about a third of its honest value, because twenty twenty-five is about a third of all-time revenue. No error, of course. The lesson: remove exactly the filters you mean to remove, and name the table you mean. You will build this exact bug on purpose in the Medium practice, watch it fail, and delete it.

[Slide 5] Time questions need time intelligence, and time intelligence has one non-negotiable requirement: a proper date table. Power BI's auto date-slash-time option fakes one — a hidden calendar per date column — and professionals turn it off, because hidden calendars bloat the model and cannot be shared across facts. Instead: one calculated table, Dates, built with the CALENDAR function from January first twenty twenty-three through December thirty-first twenty twenty-six — one thousand four hundred sixty-one rows, one per day. Add Year and Month columns, sort Month by its number so April stops leading the year, and then the step everyone forgets: right-click, Mark as date table. Marking is the license — it is what tells the time functions this table can be trusted. Relate it to sales, and the star has a time spine.

[Slide 6] With the spine marked, two functions carry most of the time questions you will ever be asked. TOTALYTD accumulates within the year: in a twenty twenty-five monthly table, the June row reads nine point nine two million — January through June. And SAMEPERIODLASTYEAR is CALCULATE's context editing applied to time: it replaces the current date window with the same window shifted back a year. Revenue LY filtered to twenty twenty-five reports twenty twenty-four's eighteen point four million; the growth measure divides the difference by the base and reads plus twelve percent. Filter to June twenty twenty-six instead and the same two measures report June twenty twenty-five and plus fifteen point nine percent. The definitions never changed. Only the context did. That is the whole promise of the measure family: write the comparison once, and every year, month, category, and region inherits it.

[Slide 7] And now the promise kept. Finance's targets table — one thousand eighty rows at region by category by month — has sat unrelated since Lesson Four, because its grain columns had no unique one-side to hang from. Tonight the model has one: TargetMonth relates to the date spine, and two tiny calculated tables — Regions, built with DISTINCT from stores, and Categories from products — become the conformed dimensions both facts share. Attainment is then one honest measure: actual revenue divided by target revenue. For the first half of twenty twenty-six: eleven and a half million of actuals against ten point seven of target — one hundred seven point four percent. Slice by region and the board slide writes itself: the West leads at one hundred twelve point seven, the Southwest trails at one hundred five — and notice the headline hiding in plain sight: every region beat its target. Three weeks of modeling, and the island is a bar chart with a hundred-percent line.

[Slide 8] Last discussion of the module, and it is a judgment call — which means there is no single right answer, and that is the lesson. Attainment is one hundred seven percent everywhere, and a manager concludes the targets were sandbagged. Ninety seconds: your move. Here is the shape of a good answer. First, the check: how were the targets set? In this company, roughly prior year plus eight percent. So beating them by seven points means growth ran around fifteen percent while the plan assumed eight. Second, the framing: that fact has two honest readings — the targets were conservative, or the performance was genuinely strong — and the data alone cannot pick between them. The analyst's job is to surface the methodology so the room argues about the right thing, and never, ever, to adjust the measure until the number flatters somebody's story. The number is the number. The argument is about what it means.

[Slide 9] Tonight in three moves: CALCULATE edits the filter context, with ALL as the filter-remover and the say-the-sentence discipline as your guardrail. One marked date table unlocked year-to-date and year-over-year in two lines apiece. And two six-row dimensions built with DISTINCT connected the last island, turning targets into a chart with a hundred-percent line that every region clears. Step back and look at what Module Two built in three weeks: a star of facts sharing dimensions, a family of named measures with one definition per concept, and a time spine that makes any of them time-aware. That is the machine, and it is finished. This week: the practice trio ends with you auditing the CEO's investor letter — bring skepticism. Assignment A-zero-six completes your model. And then Module Two closes with the midterm and the first project, where a messy pile of files becomes a working model with nobody holding your hand. You are ready. Next module, we finally spend all of this on what everyone thinks BI is: the charts.