DAX Patterns: Time, Variables, and Beyond

Building On Context

Everything you've learned so far leads here

You've built the foundation. Now it's time to put it to work. Here's what we're covering:

Time intelligence for period comparisons (YTD, Prior Year, QTD)
Variables for cleaner, faster, more debuggable formulas
Relationships -- active, inactive, and virtual via TREATAS
Calculation groups to eliminate duplication across your entire model

Part 1

Understanding DAX

DAX vs Excel syntax
Measures vs calculated columns
Star schema and filter propagation

Part 2

How DAX Thinks

Filter context and row context
CALCULATE and context modification
Iterators and context transition

Part 3 (You Are Here)

DAX Patterns

Time intelligence functions
Variables and SWITCH patterns
Calc groups and engine internals

Time Intelligence

Comparing data across time periods -- the most common reporting need

Prerequisite: A proper date table. Time intelligence functions need a table with contiguous dates -- every single day, no gaps. Mark it as a date table in your model. Without this, functions like DATESYTD won't work correctly.

These functions return tables of dates. You pass them into CALCULATE, and CALCULATE uses those dates to override the current filter context.

              Sales YTD = CALCULATE( [Total Sales], DATESYTD('Calendar'[Date]) )

              Sales PY = CALCULATE( [Total Sales], DATEADD('Calendar'[Date], -1, YEAR) )

Function Reference

DATESYTD( 'Calendar'[Date] [, "MM/DD"] )

Returns all dates from January 1 (or your fiscal year start) through the last date in the current filter context. Resets on Jan 1 each year. Optional second parameter sets a custom fiscal year end date.

Example: On Mar 15, returns Jan 1 - Mar 15 (75 days).

DATESQTD( 'Calendar'[Date] )

Returns dates from the current quarter start through the last date in context. Resets at each quarter boundary (Q1=Jan, Q2=Apr, Q3=Jul, Q4=Oct).

Example: On Mar 15 (Q1), returns Jan 1 - Mar 15.

DATESMTD( 'Calendar'[Date] )

Returns dates from the 1st of the current month through the last date in context. Resets on the first of each month.

Example: On Mar 15, returns Mar 1 - Mar 15 (15 days).

DATEADD( 'Calendar'[Date], <intervals>, <YEAR|QUARTER|MONTH|DAY> )

Shifts the entire date filter by the specified interval. Use -1 YEAR for prior year, -1 QUARTER for prior quarter, etc. The most flexible time intelligence function.

Example: DATEADD(..., -1, YEAR) on Mar 15, 2024 returns Mar 15, 2023.

Interactive Calendar

Click any date to set it as "today" and see what each function returns, including calculated dollar amounts.

YTD QTD MTD

Click a date to begin

Variables in DAX

Write once, use many -- cleaner code and better performance

Variables store intermediate results, give them names, and prevent recalculation. The result is code that's easier to read, debug, and often faster.

              Profit Margin =

              VAR Sales = [Total Sales]

              VAR Cost = [Total Cost]

              VAR Profit = Sales - Cost

              VAR Result = DIVIDE( Profit, Sales )

              RETURN Result

Variables capture context at definition time. When you write VAR CurrentSales = [Total Sales] and then use CALCULATE to shift the date context, CurrentSales still holds the value from the original context. This makes before-and-after comparisons clean and predictable -- crucial for time intelligence patterns.

Step Through the Evaluation

VAR Sales = [Total Sales]

VAR Cost = [Total Cost]

VAR Profit = Sales - Cost

VAR Result = DIVIDE( Profit, Sales )

RETURN Result

Profit Margin

--

Click "Step" to walk through how DAX evaluates this measure. Each variable is computed once and reused.

The SWITCH + VAR Pattern

Combine variables with SWITCH for efficient conditional logic. The value is evaluated once, then tested multiple times without recalculation.

              VAR SalesValue = [Total Sales]

              RETURN

              SWITCH( TRUE(),

                  SalesValue > 1000000, "High",

                  SalesValue > 500000, "Medium",

                  "Low"

              )

SalesValue is evaluated ONCE, then referenced 3 times in SWITCH. Without a variable, [Total Sales] would be recalculated for each condition.

Relationships & USERELATIONSHIP

Multiple date columns, one active relationship, and how to switch

Your Sales table has an OrderDate and a ShipDate. Both connect to Calendar. But Power BI only allows one active relationship between two tables. The fix: create inactive relationships and activate them on demand with USERELATIONSHIP.

Active vs Inactive Paths

Toggle a path to switch the active relationship and see how the numbers shift.

$1.5M $1.0M $0.5M 0

$1.34M

$1.18M

$1.29M

$1.37M

$1.41M

$1.30M

$1.05M

$1.23M

Q1 Q2 Q3 Q4

Order Date Ship Date

Total Sales = SUM( 'Sales'[Amount] )

Tip: Instead of USERELATIONSHIP, many teams use a Calculation Group to switch date paths. See the Calculation Groups section below.

TREATAS: Virtual Relationships

No physical relationship? TREATAS applies a virtual filter from one table to another.

              Budget vs Actual =

              CALCULATE(

                  [Budget Amount],

                  TREATAS( VALUES('Calendar'[Year]), 'Budget'[FiscalYear] )

              )

Takes the currently selected Calendar[Year] values and applies them as a filter on Budget[FiscalYear]. No physical relationship needed.

Logic Patterns

SWITCH, SELECTEDVALUE, and making reports dynamic

SWITCH: The Better IF Chain

Nested IFs (Messy)

                  IF([Sales] > 1000000, "High",

                    IF([Sales] > 500000, "Medium",

                      "Low"

                    )

                  )

vs

SWITCH (Clean)

                  SWITCH( TRUE(),

                    [Sales] > 1000000, "High",

                    [Sales] > 500000, "Medium",

                    "Low"

                  )

Order matters -- SWITCH returns the first match. Put your most restrictive condition first.

SELECTEDVALUE: Slicer-Driven Logic

Returns a value when a column is filtered to exactly one value. Multi-select or no selection returns the fallback.

Slicer: Year

Sales for All Years

= "Sales for " & SELECTEDVALUE('Calendar'[Year], "All Years") vs "Sales for All Years"

Common SELECTEDVALUE Uses

Dynamic report titles that change based on slicer selections -- great for exported PDFs.
What-If parameters. SELECTEDVALUE reads the slider position and feeds it into calculations.
Measure switching. SWITCH + SELECTEDVALUE on a disconnected table lets one visual show multiple metrics.

Field Parameters

Field parameters let users dynamically change which measure or column appears in a visual -- no SWITCH logic needed. Created from the Modeling ribbon, they use NAMEOF() references under the hood. Power BI's slicer integration handles the rest.

Calculation Groups

Write time intelligence once, apply it to every measure

Think of a calculation group as a modifier that wraps around any measure. You write your base measures once (Sales, Cost, Profit), then the calculation group applies transformations like YTD, Prior Year, or YoY% to whichever measure is in the visual. Instead of 3 measures x 3 time views = 9 measures, you maintain 3 base measures + 1 calc group.

The Duplication Problem

Without Calc Groups

Sales YTD Cost YTD Profit YTD Sales PY Cost PY Profit PY Sales YoY% Cost YoY% Profit YoY%

9 measures (and growing)

vs

With Calc Groups

Sales Cost Profit

×

Actual YTD Prior Year YoY %

3 base × 4 items = all combos

SELECTEDMEASURE()

The function that makes it work. It returns whichever measure the user placed in the visual:

              -- This ONE item applies YTD to ANY measure

              YTD = CALCULATE( SELECTEDMEASURE(), DATESYTD('Calendar'[Date]) )

See It in Action

Select a calculation item to see how it transforms the base measure. Notice the format change on YoY%.

Base Measure

[Total Sales]

×

Calculation Item

                      -- Actual: No modification

                      SELECTEDMEASURE()

Currency

$5,082,000

The Actual item returns the measure unchanged. SELECTEDMEASURE() evaluates [Total Sales] directly.

Under the Hood

How the DAX engine processes your queries in two stages

Every time a visual renders, DAX runs a two-stage process. Understanding this changes how you think about performance.

Storage Engine

Retrieves data from the in-memory VertiPaq model. Columnar, compressed, parallelized across cores.

Formula Engine

Evaluates DAX expressions -- context logic, CALCULATE, iterators, variables. Single-threaded.

Result

Final value for your visual. More work in Stage 1 = faster overall.

Fast vs Slow Patterns

Storage Engine Friendly Fast

Simple aggregations (SUM, COUNT, MIN, MAX)
Star schema with clean foreign keys
Direct column references
CALCULATE with table filters

Formula Engine Heavy Slower

Complex iterators (SUMX with nested logic)
Nested CALCULATE inside CALCULATE
Row-by-row processing patterns
Many-to-many without bridge tables

The performance rule of thumb: Push as much work to the Storage Engine as possible. Star schema + simple aggregations = the Storage Engine does the heavy lifting = fast. When a measure feels slow, ask: "Can I restructure this to let the Storage Engine do more?"

Key Takeaways

Three parts, one complete foundation

Time intelligence returns tables. DATEADD, SAMEPERIODLASTYEAR, and friends return date tables that CALCULATE uses as filters -- not scalar values.
Variables capture context at definition. Write once, reuse many times. The value is frozen when VAR is declared, not when it's referenced.
USERELATIONSHIP activates inactive paths. One active relationship per pair of tables; switch to alternates inside CALCULATE on demand.
Calculation groups eliminate duplication. One calc group with items (YTD, PY, YoY%) replaces dozens of nearly-identical measures.
Push work to the Storage Engine. Simple aggregations on star schema = parallelized and fast. Complex DAX = single-threaded Formula Engine.
These patterns build on context. Every technique here -- time intel, variables, relationships, calc groups -- relies on the filter context and CALCULATE foundations from Parts 1 and 2.

Tools for the Next Step

DAX Studio

Free external tool for writing/testing DAX queries with performance tracing and storage engine diagnostics.

Free Tool

DAX Query View

Built into Power BI Desktop. Write DAX queries and see results without any external installation.

Built-in

Performance Analyzer

Shows exactly how long each visual takes, split between storage engine and formula engine time.

Built-in

Back to Part 2

How DAX Thinks: Context is Everything

Filter context, row context, CALCULATE, iterators, and context transition.

Part 2 of 3

Restart: Part 1

Understanding DAX

The language, syntax, data model, and how filters flow through relationships.

Part 1 of 3

You've worked through all three parts. The concepts you've internalized -- context, CALCULATE, variables, time intelligence -- are the same building blocks professionals use every day. Everything from here is applying these patterns to new problems. You've got the foundation. Now go build something great.