Excel Lesson 34 – DAX Advanced | Dataplexa

Lesson 34 · Power Pivot Advanced

DAX Advanced

Once you understand CALCULATE and filter context, the full power of DAX opens up. This lesson covers the patterns that turn a basic Power Pivot model into a professional analytical tool: time intelligence for year-to-date and period comparisons, RANKX for dynamic ranking, VAR and RETURN for clean readable measures, and ITERATORS — the X functions that apply row-by-row calculations across a filtered table. These are the building blocks of every serious DAX measure you will write in practice.

VAR and RETURN — Writing Readable Measures

Complex DAX measures quickly become unreadable when you nest expressions inside each other multiple times. VAR lets you name intermediate calculations, and RETURN tells DAX which value to output. This is the DAX equivalent of LET() in Excel — and it makes a dramatic difference to readability, debugging, and performance, since each variable is calculated only once even if referenced multiple times.

Without VAR — hard to read and error-prone:
Profit Margin % :=
  DIVIDE(
    SUM(Sales[Revenue]) - SUM(Sales[Cost]),
    SUM(Sales[Revenue]),
    0
  )

With VAR — clean, self-documenting, each piece calculated once:
Profit Margin % :=
  VAR TotalRevenue = SUM(Sales[Revenue])
  VAR TotalCost    = SUM(Sales[Cost])
  VAR TotalProfit  = TotalRevenue - TotalCost
  RETURN
    DIVIDE(TotalProfit, TotalRevenue, 0)

VAR rules:
  - Variables are defined with VAR Name = expression
  - RETURN must appear exactly once, at the end
  - Variables can reference earlier variables in the same measure
  - Variables capture the filter context at the point they are defined
  - Use VAR for any measure with more than two sub-calculations

Profit Margin % — North: 34.2% · East: 31.7% · West: 27.2% · Grand Total: 31.4% — identical result to nested version, far easier to maintain

A particularly powerful use of VAR is storing the current filter context value so you can reference it inside a CALCULATE that would otherwise modify it. For example, storing VAR CurrentRegion = SELECTEDVALUE(Customers[Region]) before entering a CALCULATE block lets you compare the current row's context against a specific value — something that is impossible without VAR in many advanced patterns.

Time Intelligence — TOTALYTD and SAMEPERIODLASTYEAR

Time intelligence functions let you calculate values across time periods relative to the current context — year-to-date totals, month-to-date, same period last year, and rolling averages. These functions require a marked Date Table in the data model and a relationship between the Date Table and the fact table's date column.

Year-to-Date revenue (resets at the start of each year):
Revenue YTD :=
  TOTALYTD([Total Revenue], DateTable[Date])

Month-to-Date revenue:
Revenue MTD :=
  TOTALMTD([Total Revenue], DateTable[Date])

Quarter-to-Date revenue:
Revenue QTD :=
  TOTALQTD([Total Revenue], DateTable[Date])

Same period last year:
Revenue LY :=
  CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DateTable[Date]))

Year-over-year growth amount:
YoY Growth :=
  [Total Revenue] - [Revenue LY]

Year-over-year growth percentage:
YoY Growth % :=
  VAR CurrentRevenue = [Total Revenue]
  VAR LastYearRevenue = [Revenue LY]
  RETURN
    DIVIDE(CurrentRevenue - LastYearRevenue, LastYearRevenue, BLANK())

Time Intelligence Measures — Monthly View With YTD and YoY

Month	Revenue	Revenue YTD	Revenue LY	YoY Growth %
Jan 2025	£142,000	£142,000	£131,000	+8.4%
Feb 2025	£138,000	£280,000	£129,000	+7.0%
Mar 2025	£156,000	£436,000	£148,000	+5.4%
Apr 2025	£129,000	£565,000	£141,000	-8.5%

Revenue YTD accumulates each month · Revenue LY pulls same month from prior year via SAMEPERIODLASTYEAR · YoY % turns red when negative

DATEADD — Flexible Period Shifting

SAMEPERIODLASTYEAR is a shortcut for year comparisons. For more control — comparing to the previous month, previous quarter, or any number of periods back or forward — use DATEADD. It shifts the current date context by a specified number of intervals.

=DATEADD(DateTable[Date], number_of_intervals, interval)

  interval options: DAY, MONTH, QUARTER, YEAR
  negative number = shift backward, positive = shift forward

Previous month revenue:
Revenue PM :=
  CALCULATE([Total Revenue], DATEADD(DateTable[Date], -1, MONTH))

Previous quarter revenue:
Revenue PQ :=
  CALCULATE([Total Revenue], DATEADD(DateTable[Date], -1, QUARTER))

Two years ago:
Revenue 2Y Ago :=
  CALCULATE([Total Revenue], DATEADD(DateTable[Date], -2, YEAR))

Month-over-month growth %:
MoM Growth % :=
  VAR Current = [Total Revenue]
  VAR PrevMonth = [Revenue PM]
  RETURN DIVIDE(Current - PrevMonth, PrevMonth, BLANK())

DATEADD is more flexible than the shorthand functions because it works at any granularity. Need to compare to two quarters ago? DATEADD(DateTable[Date], -2, QUARTER). Need to see what happened 30 days before? DATEADD(DateTable[Date], -30, DAY). The shorthand functions (TOTALYTD, SAMEPERIODLASTYEAR) are cleaner when they match your need exactly — DATEADD is the tool when they do not.

RANKX — Dynamic Ranking

RANKX ranks rows in a table based on the value of an expression, within the current filter context. Unlike Excel's RANK function which operates on a fixed range, RANKX is fully dynamic — the ranking updates automatically when the PivotTable is filtered by date, region, or any other dimension.

=RANKX(table, expression, [value], [order], [ties])

  table       → The table to rank over (often ALL of a column)
  expression  → The value to rank by (usually a measure)
  value       → The value to rank (leave blank to use current context)
  order       → 0 = descending (highest = rank 1), 1 = ascending
  ties        → Skip (default) or Dense (no gaps in rank numbers)

Rank customers by their total revenue (1 = highest):
Customer Rank :=
  RANKX(
    ALL(Customers[CustomerName]),
    [Total Revenue],
    ,
    0,
    Dense
  )

Rank products within current region filter (context-aware):
Product Rank in Region :=
  RANKX(
    ALLSELECTED(Products[ProductName]),
    [Total Revenue],
    ,
    0,
    Dense
  )

Note on ALL vs ALLSELECTED:
  ALL(column)         → ranks across all values, ignoring any slicer
  ALLSELECTED(column) → ranks only within the currently selected values
                        — respects slicer selections

RANKX — Customer Revenue Ranking (Dynamic)

Customer	Total Revenue	Rank
Apex Corp	£312,000	🥇 1
Meridian Ltd	£289,000	🥈 2
Vantage Group	£271,000	🥉 3
Lynx Partners	£214,000	4
Orbit Supplies	£193,000	5

Rank updates automatically when a Region or Date slicer is applied — no formula changes required

ITERATOR Functions — The X Functions

Iterator functions — often called "X functions" — evaluate an expression row by row across a table and then aggregate the results. They are used whenever you need to apply a calculation at the row level before summing or averaging. The most important iterators are SUMX, AVERAGEX, MAXX, MINX, and COUNTX.

=SUMX(table, expression)
  Iterates over every row in table, evaluates expression for each row,
  then sums the results.

Why you need SUMX — the weighted average problem:

Wrong approach (SUM then divide — mathematically incorrect for weighted avg):
Avg Unit Price := DIVIDE(SUM(Sales[Revenue]), SUM(Sales[Quantity]))

Correct approach with SUMX (price × qty per row, then sum):
Total Revenue Check :=
  SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
  (recalculates revenue row by row — useful when Revenue column is missing)

Weighted average revenue per unit sold:
Weighted Avg Price :=
  DIVIDE(
    SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
    SUM(Sales[Quantity])
  )

AVERAGEX — average of a row-level expression:
Avg Profit per Order :=
  AVERAGEX(Sales, Sales[Revenue] - Sales[Cost])
  (calculates profit for each order row, then averages those profits)

MAXX / MINX — find the row with the max or min expression value:
Largest Order :=
  MAXX(Sales, Sales[Revenue])

Most Profitable Product :=
  MAXX(
    VALUES(Products[ProductName]),
    [Total Profit]
  )

SUM vs SUMX — Why the Difference Matters

❌ SUM approach — wrong for row-level multiply

SUM(Qty) * SUM(Price)

Multiplies the totals together — not the same as multiplying per row. Only works when all items have the same unit price.

✅ SUMX approach — correct row-level multiply

SUMX(Sales, [Qty] * [Price])

Multiplies Qty × Price for each individual row, then sums those products. Correct regardless of price variation across rows.

Putting It Together — A Full Advanced Measure Set

Here is a realistic set of measures for a sales dashboard, showing how VAR, time intelligence, RANKX, and SUMX work together in a coherent model. This is the kind of measure library you would build for a real reporting project.

-- Core measures
Total Revenue    := SUM(Sales[Revenue])
Total Cost       := SUM(Sales[Cost])
Order Count      := COUNTROWS(Sales)

-- Profit measures using VAR
Gross Profit :=
  VAR Rev  = [Total Revenue]
  VAR Cost = [Total Cost]
  RETURN Rev - Cost

Profit Margin % :=
  VAR Rev    = [Total Revenue]
  VAR Profit = [Gross Profit]
  RETURN DIVIDE(Profit, Rev, 0)

-- Time intelligence
Revenue YTD  := TOTALYTD([Total Revenue], DateTable[Date])
Revenue LY   := CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DateTable[Date]))
YoY Growth % :=
  VAR Curr = [Total Revenue]
  VAR LY   = [Revenue LY]
  RETURN DIVIDE(Curr - LY, LY, BLANK())

-- Share of total
Revenue % of Total :=
  DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(Sales)), 0)

-- Ranking
Customer Rank :=
  RANKX(ALL(Customers[CustomerName]), [Total Revenue], , 0, Dense)

-- Row-level calculation
Avg Profit per Order :=
  AVERAGEX(Sales, Sales[Revenue] - Sales[Cost])

Full measure library — all 11 measures respond dynamically to any slicer, row, or column filter applied in the PivotTable · no formula edits ever needed

💡 Teacher's Note

The single habit that separates good DAX writers from great ones is using VAR everywhere. Even when you do not technically need it — even for a two-line measure — naming your sub-calculations makes the measure self-documenting and dramatically easier to debug. When something returns the wrong number, you can test each VAR independently by temporarily making it the RETURN value to see exactly where the logic breaks down. Treat VAR/RETURN not as an advanced technique but as the default way to write any measure that involves more than a single function call.

🟠 Practice

Q1. Rewrite this measure using VAR and RETURN to make it readable:
:= DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue]), 0)

Q2. Write a measure called "Revenue PM" that returns last month's total revenue using DATEADD.

Q3. Why would you use SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) instead of SUM(Sales[Quantity]) * SUM(Sales[UnitPrice])?

Next up — Measures in Power Pivot, where we bring everything together into a complete measure library — KPI measures, conditional formatting via DAX, dynamic measure selection, and the patterns that make a Power Pivot model production-ready.

← Previous Course Index Next →

Excel Course