CodeVerse Creatives
Esc
Excel, SQL & Power BI Syllabus / Module 10 · DAX / Week 10

Week 10: DAX — Calculated Columns, Measures & Time Intelligence

A model with the right tables and relationships (Week 9) still can't answer a single question on its own — DAX (Data Analysis Expressions) is the formula language that makes it actually compute something, the direct successor to Week 1's Excel formulas but built around a model instead of individual cells. This week covers writing real measures, the filter-context idea that trips up almost everyone at first, and the time-intelligence patterns — YTD, month-over-month, year-over-year — that answer the questions stakeholders ask most.

Module 10 of 12 Week 10 of 12 ~3–4 Hours Hands-on Exercise Included

By the end of this week, you'll be able to

  • Explain the difference between a calculated column and a measure, and pick the right one
  • Use CALCULATE to override filter context
  • Write YTD, month-over-month and year-over-year measures against a proper date table
1 Calculated Columns vs. Measures 2 Writing Your First Measures 3 CALCULATE & Filter Context 4 Building a Proper Date Table 5 Time Intelligence: YTD, MoM & YoY 6 Exercise 7 Knowledge Check

1. Calculated Columns vs. Measures

Both are written in DAX and both can appear in a visual, but they're computed completely differently:

  • Calculated column — computed once per row, at refresh time, and physically stored in the table, like an Excel formula column filled down every row.
  • Measure — computed on the fly, for whatever rows are currently in view (a visual, a filter, a slicer selection), and never stored.
dax
-- Calculated column -- one value per row, stored, only usable AS a row value
Price Tier =
IF(Orders[amount] >= 500, "High", IF(Orders[amount] >= 100, "Medium", "Low"))

-- Measure -- recalculated for whatever's currently filtered, never stored
Total Sales = SUM(Orders[amount])

Price Tier has to be a calculated column because each order genuinely needs its own bucket, sliceable and filterable like any other column. Total Sales has to be a measure — there's no single "total" to store per row; it only means something once it's aggregated across whatever rows a visual currently shows. The rule of thumb: default to a measure, and reach for a calculated column only when the value needs to exist as a discrete, filterable field on its own row.

2. Writing Your First Measures

New Measure (Home tab, or right-click a table in the Fields pane) opens a formula bar identical in spirit to Excel's:

dax
Total Sales = SUM(Orders[amount])

Order Count = COUNTROWS(Orders)

Average Order Value = AVERAGE(Orders[amount])

-- DIVIDE is the safe way to divide in DAX -- it returns the third
-- argument (here, 0) instead of an error when the denominator is 0
Safe Average Order Value = DIVIDE(SUM(Orders[amount]), COUNTROWS(Orders), 0)

Dragging any of these onto a card or a table visual immediately recalculates it for whatever's currently filtered — pick a Region slicer, and Total Sales recomputes for just that region, with no formula change required. That recalculate-per-context behavior is filter context, covered properly next.

3. CALCULATE & Filter Context

Filter context is simply "whatever filters currently apply" — the slicers selected, the row/column a visual is currently rendering, a filter pane selection — all combined. Every measure is evaluated fresh inside whatever filter context it's placed in. CALCULATE is the function that lets a measure change that context on purpose:

dax
Electronics Sales =
CALCULATE(SUM(Orders[amount]), Orders[category] = "Electronics")

-- ^ Regardless of whatever category a visual is currently sliced by,
--   this measure ALWAYS computes sales for Electronics specifically

Pct of Total Sales =
DIVIDE(SUM(Orders[amount]), CALCULATE(SUM(Orders[amount]), ALL(Orders)))

-- ^ ALL(Orders) strips every filter off the Orders table for that one
--   calculation, so the denominator is always the grand total --
--   this is the standard "percent of total" pattern in DAX

ALL() is the counterpart worth knowing alongside CALCULATE: it removes filters rather than adding them, which is exactly what a "percent of total" or "compared to the overall average" measure needs — the numerator sees the current filter context, the denominator deliberately ignores it.

4. Building a Proper Date Table

Every time-intelligence function in Section 5 requires one specific thing: a real date table, marked as such, related to the fact table's date column. Power BI's automatic date hierarchy isn't enough — it can't be marked as an official Date Table, and breaks down the moment more than one date column exists in the model.

dax -- a new table, generated entirely in DAX
DateTable = CALENDAR(DATE(2024, 1, 1), DATE(2026, 12, 31))

-- Then add calculated columns for the pieces reports actually use
Year = YEAR(DateTable[Date])
Month Name = FORMAT(DateTable[Date], "MMMM")
Month Number = MONTH(DateTable[Date])

After creating it: relate DateTable[Date] to Orders[order_date] in Model view (Week 9's relationship skill, applied to a table built by a formula instead of imported), then in the Data view's ribbon, use Mark as Date Table and confirm the Date column. Only after that step will functions like TOTALYTD behave correctly.

The date table needs every single calendar day, with no gaps

CALENDAR() already guarantees this, but if a date table is ever built by hand instead, a missing day will silently break time-intelligence functions for periods that cross it — they rely on being able to walk the calendar continuously, not just the days that happen to have orders.

5. Time Intelligence: YTD, MoM & YoY

With a marked date table in place, Power BI's built-in time-intelligence functions become available — each one is really just CALCULATE paired with a function that shifts or expands the date filter:

dax
Sales YTD = TOTALYTD(SUM(Orders[amount]), DateTable[Date])

-- Same idea, more explicit -- useful once a custom fiscal year matters
Sales YTD (explicit) =
CALCULATE(SUM(Orders[amount]), DATESYTD(DateTable[Date]))

Sales Same Month Last Year =
CALCULATE(SUM(Orders[amount]), SAMEPERIODLASTYEAR(DateTable[Date]))

Sales YoY % Change =
DIVIDE(
  [Total Sales] - [Sales Same Month Last Year],
  [Sales Same Month Last Year]
)

Sales Prev Month = CALCULATE(SUM(Orders[amount]), PREVIOUSMONTH(DateTable[Date]))

Sales MoM % Change =
DIVIDE([Total Sales] - [Sales Prev Month], [Sales Prev Month])

Every one of these follows the same shape as Week 7's SQL LAG pattern: compute the current period, compute a shifted comparison period, subtract and divide — DAX just hides the "find the matching prior row" logic inside a named function instead of an explicit window function.

Referencing [Total Sales] inside another measure is normal DAX style

Square-bracket references like [Total Sales] call an already-defined measure from inside a new one — this is the standard way to build MoM/YoY measures on top of a base measure rather than repeating SUM(Orders[amount]) everywhere, exactly like Week 8's views kept SQL logic from being retyped.

6. Hands-on Exercise

Hands-on

Add a full measure layer to Week 9's model

Build on the star-schema model from last week's exercise.

Requirements:

  1. Create a DateTable with CALENDAR(), add Year and Month Name calculated columns, relate it to Orders[order_date], and mark it as the official date table.
  2. Write base measures: Total Sales, Order Count, and a safe Average Order Value using DIVIDE.
  3. Write a Pct of Total Sales measure using CALCULATE and ALL().
  4. Write Sales YTD using TOTALYTD.
  5. Write Sales MoM % Change using PREVIOUSMONTH, referencing your Total Sales measure rather than repeating SUM(...).
  6. Add one calculated column, Price Tier, and write one sentence explaining why it had to be a column and not a measure.
Hint

Drop Sales YTD and Sales MoM % Change onto a simple table visual with Month Name on rows before moving on — seeing the numbers change believably month to month is the fastest way to confirm the date table relationship and the time-intelligence functions are actually wired up correctly.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

Q1

You need a "Price Tier" label on every order row, filterable in a slicer. Calculated column or measure?

A calculated column. It needs to exist as a real, discrete value on every row so it can be used in a slicer or as a row grouping — a measure only produces an aggregated number for whatever's currently in view, and can't be sliced by in that way.

Q2

What does ALL(Orders) do inside a CALCULATE call?

It removes every filter currently applied to the Orders table for that specific calculation, regardless of what slicers or visual context are active elsewhere. It's the standard way to force a "grand total" calculation inside a "percent of total" measure, where the denominator must always ignore the current filter context.

Q3

Why won't TOTALYTD work correctly against a date column that hasn't been marked as an official Date Table?

Time-intelligence functions need to walk a continuous calendar to know which days belong to "this year so far" — marking a table as the official Date Table tells Power BI which table and column to treat that way, and the table needs one row for every calendar day with no gaps for that walk to be reliable.

Q4

What does referencing [Total Sales] (in square brackets) inside another measure's formula do?

It calls the already-defined Total Sales measure from inside the new measure, exactly like calling a named formula. This is the standard way to build derived measures (MoM, YoY, percent-of-total) on top of a base measure without retyping its underlying aggregation everywhere it's needed.

← Previous Week 9: Power BI Fundamentals — Importing & Modeling Data Next Week 11: Building & Publishing Interactive Power BI Dashboards