Time, Variables, and Beyond
Powerful patterns that turn DAX knowledge into real-world solutions.
Everything you've learned so far leads here
You've built the foundation. Now it's time to put it to work.
Period comparisons: YTD, Prior Cycle, QTD
Cleaner, faster, more debuggable formulas
Active, inactive, and virtual via USERELATIONSHIP
Eliminate duplication across your entire model
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.
These functions return tables of dates. You pass them into CALCULATE, and CALCULATE uses those dates to override the current filter context.
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).
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.
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).
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, 2226 returns Mar 15, 2225.
Click any date to set it as "today" and see what each function returns, including calculated dollar amounts.
SWITCH, SELECTEDVALUE, and making reports dynamic
Order matters: SWITCH returns the first match. Put restrictive conditions first. Storing [Revenue] in a VAR means it evaluates once, not twice.
Field parameters let users dynamically change which measure or column appears in a visual without any SWITCH logic. Created from the Modeling ribbon, they use NAMEOF() references under the hood. Power BI's slicer integration handles the rest.
Returns a value when a column is filtered to exactly one value. Multi-select or no selection returns the fallback.
Output: "Revenue for All Cycles"
Report titles that change based on slicer selections. Great for exported PDFs.
SELECTEDVALUE reads the slider position and feeds it into calculations.
One visual, multiple metrics via a disconnected slicer table.
Write once, use many: cleaner code and better performance
Variables store intermediate results, give them names, and prevent recalculation. You just saw SWITCH reference [Revenue] twice in Logic Patterns. With a variable, that value is calculated once and reused every time.
Variables capture context at definition time. CurrentRevenue is evaluated once and referenced in three SWITCH conditions. Without the variable, [Total Revenue] would recalculate for each condition check.
Multiple date columns, one active relationship, and how to switch
Your Trades table has a TradeDate and a DeliveryDate. Both connect to Stardate. But Power BI only allows one active relationship between two tables. The fix: create inactive relationships and activate them on demand with USERELATIONSHIP.
Toggle a path to switch the active relationship and see how the numbers shift.
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 (Revenue, Cost, Profit), then the calculation group applies transformations like YTD, Prior Cycle, 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 function that makes it work. It returns whichever measure the user placed in the visual:
Select a calculation item to see how it transforms the base measure. Notice the format change on YoY%.
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.
Retrieves data from the in-memory VertiPaq model. Columnar, compressed, parallelized across cores.
Evaluates DAX expressions: context logic, CALCULATE, iterators, variables. Single-threaded.
Final value for your visual. More work in Stage 1 = faster overall.
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?"
Three parts, one complete foundation
DATEADD, SAMEPERIODLASTYEAR, and friends return date tables that CALCULATE uses as filters, not scalar values.
Write once, reuse many times. The value is frozen when VAR is declared, not when it is referenced.
One active relationship per pair of tables; switch to alternates inside CALCULATE on demand.
One calc group with items (YTD, PY, YoY%) replaces dozens of nearly identical measures.
Simple aggregations on star schema are parallelized and fast. Complex DAX runs single-threaded in the Formula Engine.
Every technique here (time intel, variables, relationships, calc groups) relies on the filter context and CALCULATE foundations from Parts 1 and 2.
Free external tool for writing/testing DAX queries with performance tracing and storage engine diagnostics.
Free ToolBuilt into Power BI Desktop. Write DAX queries and see results without any external installation.
Built-inShows exactly how long each visual takes, split between storage engine and formula engine time.
Built-inYou'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. The rest is practice.