DAX Advanced

Time Intelligence Functions

Time intelligence is one of DAX's most powerful capabilities. It lets you compare periods, calculate running totals, and perform year-over-year analysis with concise formulas.

Prerequisites

Before using any time intelligence function, you must have:

A dedicated Date table with a contiguous date column (no gaps, no duplicates)
The Date table marked as a Date Table (Table tools → Mark as date table)
A relationship between the Date table and your fact table

If any of these are missing, time intelligence functions will return incorrect results or errors.

TOTALYTD — Year-to-Date Total

Calculates the running total from the beginning of the year to the current date in context.

// Syntax: TOTALYTD(Expression, Dates, [Filter], [YearEndDate])

Revenue YTD =
TOTALYTD(
    SUM(FactSales[TotalAmount]),
    DimDate[Date]
)

// With fiscal year ending June 30
Revenue Fiscal YTD =
TOTALYTD(
    SUM(FactSales[TotalAmount]),
    DimDate[Date],
    "6/30"
)

How it works: If the current filter context is March 2026, TOTALYTD calculates the sum from January 1, 2026 to March 31, 2026.

TOTALQTD — Quarter-to-Date Total

Revenue QTD =
TOTALQTD(
    SUM(FactSales[TotalAmount]),
    DimDate[Date]
)

TOTALMTD — Month-to-Date Total

Revenue MTD =
TOTALMTD(
    SUM(FactSales[TotalAmount]),
    DimDate[Date]
)

SAMEPERIODLASTYEAR

Returns a set of dates shifted back by one year from the current filter context.

Revenue Last Year =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    SAMEPERIODLASTYEAR(DimDate[Date])
)

If the current context is Q1 2026, this returns revenue for Q1 2025.

PREVIOUSMONTH / PREVIOUSQUARTER / PREVIOUSYEAR

Return dates for the previous month, quarter, or year.

Revenue Prev Month =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    PREVIOUSMONTH(DimDate[Date])
)

Revenue Prev Quarter =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    PREVIOUSQUARTER(DimDate[Date])
)

Revenue Prev Year =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    PREVIOUSYEAR(DimDate[Date])
)

DATEADD

Shifts a set of dates by a specified interval. More flexible than SAMEPERIODLASTYEAR.

// Syntax: DATEADD(Dates, NumberOfIntervals, Interval)
// Intervals: DAY, MONTH, QUARTER, YEAR

// Revenue 3 months ago
Revenue 3 Months Ago =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DATEADD(DimDate[Date], -3, MONTH)
)

// Revenue 2 years ago
Revenue 2 Years Ago =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DATEADD(DimDate[Date], -2, YEAR)
)

DATESYTD

Returns a table of dates from the beginning of the year to the last date in the current context.

Revenue YTD v2 =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DATESYTD(DimDate[Date])
)
// Equivalent to TOTALYTD but using CALCULATE + DATESYTD

DATESBETWEEN

Returns dates between two specific dates. Useful for custom date ranges.

// Revenue for a specific date range
Revenue Q1 2026 =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DATESBETWEEN(DimDate[Date], DATE(2026, 1, 1), DATE(2026, 3, 31))
)

// Dynamic: Last 30 days from today
Revenue Last 30 Days =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DATESBETWEEN(DimDate[Date], TODAY() - 30, TODAY())
)

PARALLELPERIOD

Returns a full parallel period shifted by the specified interval (always returns complete periods, unlike DATEADD).

// Revenue for the entire previous year (not same period last year)
Revenue Full Prev Year =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    PARALLELPERIOD(DimDate[Date], -1, YEAR)
)

DATEADD vs PARALLELPERIOD:

Feature	DATEADD	PARALLELPERIOD
Shifts by	Exact dates	Complete periods
Example	If context is Jan 15-Mar 15, shifts each date back by 1 year	If context is Jan 15-Mar 15, returns full Jan 1-Mar 31 of previous year
Best for	Same-period comparisons	Full-period comparisons

Time Intelligence Functions Summary

Function	Description	Example Use Case
`TOTALYTD`	Year-to-date total	YTD revenue dashboard
`TOTALQTD`	Quarter-to-date total	Quarterly progress tracking
`TOTALMTD`	Month-to-date total	Monthly progress tracking
`SAMEPERIODLASTYEAR`	Same period, prior year	Year-over-year comparison
`PREVIOUSMONTH`	Prior month dates	Month-over-month comparison
`PREVIOUSQUARTER`	Prior quarter dates	Quarter-over-quarter comparison
`PREVIOUSYEAR`	Prior year dates	Annual comparison
`DATEADD`	Shift dates by interval	Flexible period comparison
`DATESYTD`	Year-to-date dates	YTD with CALCULATE
`DATESBETWEEN`	Dates in a range	Custom date ranges
`PARALLELPERIOD`	Complete shifted period	Full prior period analysis

Year-over-Year Calculations

Year-over-year (YoY) analysis is one of the most common business requirements. Here's how to build a complete set of YoY measures.

YoY Change (Absolute)

YoY Change =
VAR CurrentRevenue = SUM(FactSales[TotalAmount])
VAR PriorYearRevenue =
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        SAMEPERIODLASTYEAR(DimDate[Date])
    )
RETURN
    CurrentRevenue - PriorYearRevenue

YoY Growth %

YoY Growth % =
VAR CurrentRevenue = SUM(FactSales[TotalAmount])
VAR PriorYearRevenue =
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        SAMEPERIODLASTYEAR(DimDate[Date])
    )
RETURN
    DIVIDE(
        CurrentRevenue - PriorYearRevenue,
        PriorYearRevenue,
        BLANK()
    )

YTD vs Prior YTD

Revenue YTD =
TOTALYTD(
    SUM(FactSales[TotalAmount]),
    DimDate[Date]
)

Revenue Prior YTD =
CALCULATE(
    TOTALYTD(
        SUM(FactSales[TotalAmount]),
        DimDate[Date]
    ),
    SAMEPERIODLASTYEAR(DimDate[Date])
)

YTD Growth % =
VAR CurrentYTD = [Revenue YTD]
VAR PriorYTD = [Revenue Prior YTD]
RETURN
    DIVIDE(CurrentYTD - PriorYTD, PriorYTD, BLANK())

Complete Time Comparison Measure Set

Here is a comprehensive set of measures for a sales dashboard:

// --- Base Measures ---
Total Revenue = SUM(FactSales[TotalAmount])

// --- Year-over-Year ---
Revenue PY =
CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))

Revenue YoY Change = [Total Revenue] - [Revenue PY]

Revenue YoY % = DIVIDE([Revenue YoY Change], [Revenue PY])

// --- Year-to-Date ---
Revenue YTD = TOTALYTD([Total Revenue], DimDate[Date])

Revenue PYTD =
CALCULATE(
    TOTALYTD([Total Revenue], DimDate[Date]),
    SAMEPERIODLASTYEAR(DimDate[Date])
)

Revenue YTD Change = [Revenue YTD] - [Revenue PYTD]

Revenue YTD % = DIVIDE([Revenue YTD Change], [Revenue PYTD])

// --- Month-over-Month ---
Revenue PM =
CALCULATE([Total Revenue], PREVIOUSMONTH(DimDate[Date]))

Revenue MoM Change = [Total Revenue] - [Revenue PM]

Revenue MoM % = DIVIDE([Revenue MoM Change], [Revenue PM])

// --- Quarter-over-Quarter ---
Revenue PQ =
CALCULATE([Total Revenue], PREVIOUSQUARTER(DimDate[Date]))

Revenue QoQ Change = [Total Revenue] - [Revenue PQ]

Revenue QoQ % = DIVIDE([Revenue QoQ Change], [Revenue PQ])

Displaying YoY in a Matrix

Create a matrix visual with:

Rows: DimDate[Year], DimDate[MonthName]
Values: Total Revenue, Revenue PY, Revenue YoY %, Revenue YTD, Revenue PYTD

This gives you a comprehensive time-comparison view:

Year  Month    Revenue    PY Revenue   YoY %    YTD       PYTD
2026  Jan      $120,000   $100,000     20.0%    $120,000  $100,000
2026  Feb      $135,000   $110,000     22.7%    $255,000  $210,000
2026  Mar      $140,000   $125,000     12.0%    $395,000  $335,000

Running Totals

A running total (cumulative sum) shows the accumulated value from the beginning of a period up to the current point.

Cumulative Sum (All Time)

Running Total =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(
        ALL(DimDate[Date]),
        DimDate[Date] <= MAX(DimDate[Date])
    )
)

How it works:

ALL(DimDate[Date]) removes date filters to see all dates
FILTER keeps only dates up to the maximum date in the current context
SUM aggregates revenue for those dates

Running Total Within Year

Running Total YTD =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(
        ALL(DimDate),
        DimDate[Date] <= MAX(DimDate[Date])
        && DimDate[Year] = MAX(DimDate[Year])
    )
)

This resets the running total at the beginning of each year.

Running Total Using DATESYTD (Simpler)

Running Total YTD v2 =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DATESYTD(DimDate[Date])
)

Cumulative Customer Count

Cumulative Customers =
CALCULATE(
    DISTINCTCOUNT(FactSales[CustomerKey]),
    FILTER(
        ALL(DimDate[Date]),
        DimDate[Date] <= MAX(DimDate[Date])
    )
)

Running Average

Running Average Revenue =
VAR CurrentDate = MAX(DimDate[Date])
VAR AllDates =
    FILTER(
        ALL(DimDate[Date]),
        DimDate[Date] <= CurrentDate
    )
VAR TotalRevenue =
    CALCULATE(SUM(FactSales[TotalAmount]), AllDates)
VAR DayCount =
    CALCULATE(
        DISTINCTCOUNT(DimDate[Date]),
        AllDates,
        FactSales[TotalAmount] > 0
    )
RETURN
    DIVIDE(TotalRevenue, DayCount)

Moving Averages

Moving averages smooth out short-term fluctuations and reveal longer-term trends.

DATESINPERIOD Pattern

DATESINPERIOD returns a set of dates starting from a reference date, going back by a specified interval. It is the key function for moving averages.

// Syntax: DATESINPERIOD(Dates, StartDate, NumberOfIntervals, Interval)

3-Month Moving Average

Revenue 3M Moving Avg =
VAR LastVisibleDate = MAX(DimDate[Date])
VAR MovingPeriod =
    DATESINPERIOD(
        DimDate[Date],
        LastVisibleDate,
        -3,
        MONTH
    )
RETURN
    CALCULATE(
        AVERAGE(FactSales[TotalAmount]),
        MovingPeriod
    )

Alternate approach using total and dividing:

Revenue 3M Moving Avg v2 =
VAR LastVisibleDate = MAX(DimDate[Date])
VAR Revenue3M =
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        DATESINPERIOD(DimDate[Date], LastVisibleDate, -3, MONTH)
    )
RETURN
    DIVIDE(Revenue3M, 3)

7-Day Moving Average

Revenue 7D Moving Avg =
VAR LastVisibleDate = MAX(DimDate[Date])
RETURN
    DIVIDE(
        CALCULATE(
            SUM(FactSales[TotalAmount]),
            DATESINPERIOD(DimDate[Date], LastVisibleDate, -7, DAY)
        ),
        7
    )

12-Month Moving Average

Revenue 12M Moving Avg =
VAR LastVisibleDate = MAX(DimDate[Date])
RETURN
    DIVIDE(
        CALCULATE(
            SUM(FactSales[TotalAmount]),
            DATESINPERIOD(DimDate[Date], LastVisibleDate, -12, MONTH)
        ),
        12
    )

When to Use Moving Averages

Period	Best For
7-day	Daily data with weekly patterns (retail, web traffic)
30-day	Daily data with monthly patterns
3-month (quarterly)	Monthly data with quarterly seasonality
12-month	Monthly data — eliminates annual seasonality

Table Functions

Table functions return tables (not scalar values). They are used inside CALCULATE, as iterator inputs, or to create calculated tables.

FILTER

Returns a table with rows that match a condition.

// FILTER(Table, Condition)

// Use in CALCULATE to apply complex filters
High Margin Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(
        FactSales,
        FactSales[TotalAmount] - FactSales[TotalCost] > 100
    )
)

// Use as iterator input
Sum of High Value Lines =
SUMX(
    FILTER(FactSales, FactSales[TotalAmount] > 500),
    FactSales[TotalAmount]
)

Performance tip: Use FILTER on a column (FILTER(ALL(DimProduct[Category]), ...)) rather than on an entire table (FILTER(DimProduct, ...)) when possible. Filtering a single column is much faster.

ALL

Returns the entire table or column without any filters. Used to create reference totals.

// ALL as a table — removes all filters from the table
Grand Total = CALCULATE(SUM(FactSales[TotalAmount]), ALL(FactSales))

// ALL on a column — removes filter from that column only
All Products Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    ALL(DimProduct[ProductName])
)

VALUES

Returns the distinct values of a column in the current filter context.

// Count of visible categories
Category Count = COUNTROWS(VALUES(DimProduct[Category]))

// Use with HASONEVALUE for conditional display
Selected Category =
IF(
    HASONEVALUE(DimProduct[Category]),
    VALUES(DimProduct[Category]),
    "Multiple"
)

DISTINCT

Returns the unique values of a column (similar to VALUES but includes BLANK if present in the data, whereas VALUES includes BLANK only if it exists due to a broken relationship).

Unique Products = COUNTROWS(DISTINCT(DimProduct[ProductName]))

ADDCOLUMNS

Adds calculated columns to a table expression.

// Create a summary table with added columns
Product Summary =
ADDCOLUMNS(
    VALUES(DimProduct[ProductName]),
    "Total Revenue", CALCULATE(SUM(FactSales[TotalAmount])),
    "Total Units", CALCULATE(SUM(FactSales[Quantity])),
    "Avg Price",
        DIVIDE(
            CALCULATE(SUM(FactSales[TotalAmount])),
            CALCULATE(SUM(FactSales[Quantity]))
        )
)

SUMMARIZE

Groups a table by specified columns and optionally adds aggregated columns.

// Group sales by year and category
Sales Summary =
SUMMARIZE(
    FactSales,
    DimDate[Year],
    DimProduct[Category],
    "Total Revenue", SUM(FactSales[TotalAmount]),
    "Order Count", COUNTROWS(FactSales)
)

Note: Microsoft recommends using SUMMARIZECOLUMNS (for top-level queries) or ADDCOLUMNS + VALUES/DISTINCT (for virtual tables in measures) instead of SUMMARIZE with extension columns. SUMMARIZE without extension columns (just grouping) is fine.

SUMMARIZECOLUMNS

An optimized function for producing grouped summary tables. Primarily used by visuals internally, but you can use it in calculated tables.

Summary Table =
SUMMARIZECOLUMNS(
    DimDate[Year],
    DimProduct[Category],
    "Revenue", SUM(FactSales[TotalAmount]),
    "Units", SUM(FactSales[Quantity])
)

TOPN

Returns the top N rows of a table based on an expression.

// Top 5 products by revenue
Top 5 Products =
TOPN(
    5,
    VALUES(DimProduct[ProductName]),
    CALCULATE(SUM(FactSales[TotalAmount])),
    DESC
)

// Revenue from top 5 products
Top 5 Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    TOPN(
        5,
        VALUES(DimProduct[ProductName]),
        CALCULATE(SUM(FactSales[TotalAmount])),
        DESC
    )
)

GENERATE and GENERATEALL

GENERATE creates a Cartesian product filtered by the relationship between two tables. GENERATEALL keeps all rows from the first table even if the second table returns empty.

// For each customer, show their most recent order date
Customer Last Order =
GENERATE(
    VALUES(DimCustomer[CustomerName]),
    ROW("Last Order", CALCULATE(MAX(FactSales[OrderDate])))
)

Set Operations: UNION, EXCEPT, INTERSECT

// UNION — combine rows from two tables
All People =
UNION(
    SELECTCOLUMNS(Customers, "Name", Customers[Name]),
    SELECTCOLUMNS(Employees, "Name", Employees[Name])
)

// EXCEPT — rows in first table but not in second
Customers Without Orders =
EXCEPT(
    VALUES(DimCustomer[CustomerKey]),
    VALUES(FactSales[CustomerKey])
)

// INTERSECT — rows in both tables
Customers With Orders =
INTERSECT(
    VALUES(DimCustomer[CustomerKey]),
    VALUES(FactSales[CustomerKey])
)

NATURALINNERJOIN and NATURALLEFTOUTERJOIN

These join two tables on columns with the same name.

// Inner join — only matching rows
Matched Records =
NATURALINNERJOIN(Table1, Table2)

// Left outer join — all rows from first table
All With Match =
NATURALLEFTOUTERJOIN(Table1, Table2)

Table Functions Quick Reference

Function	Returns	Key Use
`FILTER`	Rows matching a condition	Complex filters in CALCULATE
`ALL`	Table/column without filters	Reference totals, removing filters
`VALUES`	Distinct values in context	Counting, iterating visible values
`DISTINCT`	Unique values from data	Similar to VALUES
`ADDCOLUMNS`	Table with extra calculated columns	Building virtual tables
`SUMMARIZE`	Grouped table	Grouping by dimensions
`SUMMARIZECOLUMNS`	Grouped summary	Optimized grouping
`TOPN`	Top N rows by expression	Top/Bottom analysis
`GENERATE`	Row-by-row evaluation	Paired calculations
`UNION`	Combined rows	Appending tables
`EXCEPT`	Rows in A not in B	Finding missing items
`INTERSECT`	Rows in both A and B	Finding common items

Context Transition

Context transition is what happens when a measure is evaluated in row context. It is one of the most subtle and powerful concepts in DAX.

What Is Context Transition?

When CALCULATE (or a measure, which implicitly wraps in CALCULATE) is evaluated inside row context (such as inside a calculated column or an iterator), the current row context is automatically converted into an equivalent filter context.

Example: Context Transition in Action

Consider a calculated column on DimProduct:

Product Revenue =
CALCULATE(SUM(FactSales[TotalAmount]))

What happens step by step:

The calculated column evaluates in row context — it processes one DimProduct row at a time
CALCULATE triggers context transition — it converts the current row's values into a filter
For row where ProductKey = 101, it effectively becomes: CALCULATE(SUM(FactSales[TotalAmount]), DimProduct[ProductKey] = 101)
The relationship between DimProduct and FactSales propagates this filter
The result is the total revenue for product 101 only

Without CALCULATE:

Product Revenue WRONG = SUM(FactSales[TotalAmount])

Without CALCULATE, there is no context transition. SUM operates in row context but there is no row context on FactSales in a DimProduct calculated column. Result: the grand total for every row.

Context Transition in Iterators

Avg Product Revenue =
AVERAGEX(
    DimProduct,
    CALCULATE(SUM(FactSales[TotalAmount]))
)

Step by step:

AVERAGEX iterates over each row in DimProduct (row context)
For each product, CALCULATE triggers context transition
The SUM is evaluated with the current product's filter
AVERAGEX averages all the per-product revenue values

Equivalent long form:

Avg Product Revenue =
AVERAGEX(
    DimProduct,
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        DimProduct[ProductKey] = EARLIER(DimProduct[ProductKey])
    )
)
// Context transition does this automatically — no need for EARLIER

When Measures Trigger Context Transition

Measures always evaluate with an implicit CALCULATE. So referencing a measure inside an iterator automatically triggers context transition:

// This measure already has implicit CALCULATE:
Total Revenue = SUM(FactSales[TotalAmount])

// When used inside AVERAGEX:
Avg Product Revenue =
AVERAGEX(
    DimProduct,
    [Total Revenue]  -- Implicit CALCULATE triggers context transition
)
// Each iteration evaluates [Total Revenue] for the current product

Performance Implications

Context transition can be expensive because:

It converts every column of the current row into a filter
If the table has many columns, many filters are created
If used in a large iterator, this happens for every row

Best practice: Be intentional about context transition. Don't add CALCULATE inside iterators unless you need it.

CALCULATE Deep Dive

Multiple Filter Arguments = AND Logic

When you pass multiple filter arguments to CALCULATE, they are combined with AND:

Electronics Revenue 2026 =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics",  -- AND
    DimDate[Year] = 2026                   -- these are combined
)
// Only rows that are Electronics AND in 2026

OR Conditions

For OR logic, you cannot simply add separate filters (they'd be AND). Instead, use one of these approaches:

Approach 1: Use || in a single filter

Electronics or Clothing Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics"
    || DimProduct[Category] = "Clothing"
)

Approach 2: Use IN operator

Electronics or Clothing Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] IN {"Electronics", "Clothing"}
)

Approach 3: Use FILTER with OR

Complex OR =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(
        ALL(DimProduct),
        DimProduct[Category] = "Electronics"
        || DimProduct[SubCategory] = "Accessories"
    )
)

Removing Filters

// Remove ALL filters
Grand Total =
CALCULATE(SUM(FactSales[TotalAmount]), REMOVEFILTERS())

// Remove filters from one table
No Product Filter =
CALCULATE(SUM(FactSales[TotalAmount]), REMOVEFILTERS(DimProduct))

// Remove filters from one column
No Category Filter =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    REMOVEFILTERS(DimProduct[Category])
)

Overriding Filters

By default, CALCULATE filters override existing filters on the same column:

Always Electronics =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics"
)
// Even if a slicer selects "Clothing", this shows Electronics revenue
// The CALCULATE filter OVERRIDES the slicer filter on Category

To intersect instead of override, use KEEPFILTERS:

Electronics If Selected =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    KEEPFILTERS(DimProduct[Category] = "Electronics")
)
// If slicer selects "Clothing", this returns BLANK
// KEEPFILTERS intersects: Electronics AND Clothing = nothing

Nested CALCULATE

You can nest CALCULATE calls. The inner CALCULATE modifies the context first, then the outer one applies its modifications:

// Revenue share: category revenue as % of year total
Category Share of Year =
VAR CategoryRevenue = SUM(FactSales[TotalAmount])
VAR YearTotal =
    CALCULATE(
        CALCULATE(
            SUM(FactSales[TotalAmount]),
            ALL(DimProduct)  -- Inner: remove product filters
        )
        // Outer CALCULATE inherits the year filter from context
    )
RETURN
    DIVIDE(CategoryRevenue, YearTotal)

Note: Nested CALCULATE can be hard to reason about. Using variables often makes the same logic clearer.

Virtual Tables in Measures

Virtual tables are table expressions created inside a measure that exist only during query evaluation. They are never stored in the model.

Using FILTER Inside Measures

Premium Product Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(
        ALL(DimProduct),
        DimProduct[ListPrice] > 200
    )
)

Using ADDCOLUMNS for Intermediate Calculations

Avg Daily Revenue =
AVERAGEX(
    ADDCOLUMNS(
        VALUES(DimDate[Date]),
        "DailyRevenue", CALCULATE(SUM(FactSales[TotalAmount]))
    ),
    [DailyRevenue]
)

Using SUMMARIZE for Grouping

Avg Customer Spend =
AVERAGEX(
    ADDCOLUMNS(
        VALUES(DimCustomer[CustomerKey]),
        "CustomerTotal", CALCULATE(SUM(FactSales[TotalAmount]))
    ),
    [CustomerTotal]
)

TREATAS

TREATAS applies the data lineage of one column to another, creating a virtual relationship. This is useful when you cannot or don't want to create a physical relationship.

Syntax

TREATAS(TableExpression, Column1, Column2, ...)

Example: Virtual Relationship

Suppose you have a Budget table that has a ProductCategory column but no direct relationship to DimProduct:

Budget Amount =
CALCULATE(
    SUM(Budget[Amount]),
    TREATAS(
        VALUES(DimProduct[Category]),
        Budget[ProductCategory]
    )
)

What TREATAS does:

Takes the current filter on DimProduct[Category] (from slicers or visual context)
Applies those values as a filter on Budget[ProductCategory]
No physical relationship required

When to Use TREATAS

Scenario	Why TREATAS?
Comparing actuals to budget	Budget table often has different granularity than the fact table
Connecting disconnected tables	When adding a relationship would cause ambiguity
Many-to-many without bridge tables	Simulating a virtual bridge
Different column names	When columns have different names but same values

TREATAS vs Physical Relationships

Feature	Physical Relationship	TREATAS
Performance	Faster (pre-computed)	Slower (computed at query time)
Visible in Model View	Yes — shown as a line	No — invisible
Affects all measures	Yes — automatic filter propagation	No — only where explicitly used
Flexibility	Fixed	Dynamic — can change per measure
Maintenance	Centralized	Scattered across measures

Best practice: Use physical relationships whenever possible. Reserve TREATAS for scenarios where physical relationships would cause problems.

Dynamic Segmentation

Dynamic segmentation lets users choose how data is grouped or analyzed at runtime, without pre-defining categories in the data model.

SWITCH + SELECTEDVALUE Pattern

Create a disconnected table (no relationships) and use it as a slicer:

// Step 1: Create a disconnected table
MetricSelector =
DATATABLE(
    "Metric", STRING,
    "SortOrder", INTEGER,
    {
        {"Revenue", 1},
        {"Profit", 2},
        {"Units Sold", 3},
        {"Avg Order Value", 4}
    }
)

// Step 2: Create a dynamic measure
Selected Metric Value =
SWITCH(
    SELECTEDVALUE(MetricSelector[Metric], "Revenue"),
    "Revenue", SUM(FactSales[TotalAmount]),
    "Profit", SUM(FactSales[TotalAmount]) - SUM(FactSales[TotalCost]),
    "Units Sold", SUM(FactSales[Quantity]),
    "Avg Order Value",
        DIVIDE(SUM(FactSales[TotalAmount]), DISTINCTCOUNT(FactSales[OrderID])),
    BLANK()
)

Now users can select "Revenue", "Profit", etc., from a slicer, and a single visual dynamically shows the chosen metric.

What-If Parameters

Power BI has a built-in What-If Parameter feature:

Go to Modelling → New Parameter
Configure the parameter name, min, max, increment, and default
Power BI creates a calculated table and a measure automatically

Example: Discount What-If

// Auto-created by Power BI:
Discount % = GENERATESERIES(0, 0.5, 0.05)
Discount % Value = SELECTEDVALUE('Discount %'[Discount %], 0.1)

// Your custom measure using the parameter:
Revenue After Discount =
SUM(FactSales[TotalAmount]) * (1 - [Discount % Value])

Disconnected Tables for Dynamic Analysis

// Create a bin size selector
BinSize =
DATATABLE(
    "BinLabel", STRING,
    "BinValue", INTEGER,
    {
        {"Small ($0-$100)", 100},
        {"Medium ($0-$500)", 500},
        {"Large ($0-$1000)", 1000}
    }
)

// Dynamic histogram measure
Customer Count by Spend =
VAR BinWidth = SELECTEDVALUE(BinSize[BinValue], 100)
RETURN
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES(DimCustomer[CustomerKey]),
            "Spend", CALCULATE(SUM(FactSales[TotalAmount]))
        ),
        [Spend] <= BinWidth
    )
)

Ranking Patterns

RANKX — Basic Ranking

// Syntax: RANKX(Table, Expression, [Value], [Order], [Ties])

Product Rank =
RANKX(
    ALL(DimProduct[ProductName]),
    CALCULATE(SUM(FactSales[TotalAmount])),
    ,
    DESC,
    DENSE
)

Ranking Tie-Breaking Methods

Method	Sequence for Tied Values	Example (values: 100, 90, 90, 80)
SKIP (default)	Ranks skip numbers after ties	1, 2, 2, 4
DENSE	Ranks don't skip	1, 2, 2, 3

Dynamic Top N Filtering

// Show only top N products in a visual
Show Top N =
VAR TopN = SELECTEDVALUE(TopNSelector[N], 10)
VAR CurrentRank =
    RANKX(
        ALL(DimProduct[ProductName]),
        CALCULATE(SUM(FactSales[TotalAmount])),
        ,
        DESC,
        DENSE
    )
RETURN
    IF(CurrentRank <= TopN, 1, 0)
// Use this measure as a visual filter: Show Top N = 1

Top N Revenue Using TOPN

Top 10 Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    TOPN(
        10,
        VALUES(DimProduct[ProductName]),
        CALCULATE(SUM(FactSales[TotalAmount])),
        DESC
    )
)

Revenue from "Others" (Everything Not in Top N)

Others Revenue =
VAR TotalRevenue = CALCULATE(SUM(FactSales[TotalAmount]), ALL(DimProduct))
VAR Top10Revenue = [Top 10 Revenue]
RETURN
    TotalRevenue - Top10Revenue

Ranking Within Groups

// Rank products within their category
Rank Within Category =
IF(
    HASONEVALUE(DimProduct[ProductName]),
    RANKX(
        ALLEXCEPT(DimProduct, DimProduct[Category]),
        CALCULATE(SUM(FactSales[TotalAmount])),
        ,
        DESC,
        DENSE
    )
)

Semi-Additive Measures

Semi-additive measures are values that cannot be summed across time (like inventory levels, account balances, and headcount). They require special DAX functions.

The Problem

Inventory Snapshot:
Date        Product    QtyOnHand
2026-01-01  Widget A   500
2026-01-02  Widget A   495
2026-01-03  Widget A   510

If you SUM QtyOnHand for January, you get 1,505 — meaningless!
You need the LAST value (510) or the FIRST value (500).

LASTDATE / FIRSTDATE

Return the last or first date in the current filter context.

// Inventory at end of period
Ending Inventory =
CALCULATE(
    SUM(FactInventory[QtyOnHand]),
    LASTDATE(DimDate[Date])
)

// Inventory at start of period
Beginning Inventory =
CALCULATE(
    SUM(FactInventory[QtyOnHand]),
    FIRSTDATE(DimDate[Date])
)

LASTNONBLANK / FIRSTNONBLANK

Similar to LASTDATE/FIRSTDATE but find the last/first date where an expression is non-blank. Essential when not every date has data.

// Last known headcount
Current Headcount =
CALCULATE(
    SUM(FactHeadcount[EmployeeCount]),
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(SUM(FactHeadcount[EmployeeCount]))
    )
)

OPENINGBALANCEYEAR / CLOSINGBALANCEYEAR

// Balance at the start of the year
Opening Balance =
OPENINGBALANCEYEAR(
    SUM(FactBalance[Balance]),
    DimDate[Date]
)

// Balance at the end of the year
Closing Balance =
CLOSINGBALANCEYEAR(
    SUM(FactBalance[Balance]),
    DimDate[Date]
)

// Monthly equivalents
Opening Balance Month =
OPENINGBALANCEMONTH(SUM(FactBalance[Balance]), DimDate[Date])

Closing Balance Month =
CLOSINGBALANCEMONTH(SUM(FactBalance[Balance]), DimDate[Date])

Semi-Additive Functions Summary

Function	Returns	Use Case
`LASTDATE`	Last date in context	End-of-period snapshots
`FIRSTDATE`	First date in context	Start-of-period snapshots
`LASTNONBLANK`	Last date with non-blank value	Sparse data (not every date has values)
`FIRSTNONBLANK`	First date with non-blank value	Sparse data
`OPENINGBALANCEYEAR`	Value on last day of previous year	Financial opening balances
`CLOSINGBALANCEYEAR`	Value on last day of current year	Financial closing balances
`OPENINGBALANCEMONTH`	Value on last day of previous month	Monthly opening balances
`CLOSINGBALANCEMONTH`	Value on last day of current month	Monthly closing balances

Performance Tips for DAX

Writing correct DAX is important. Writing fast DAX is equally important when your model grows to millions of rows.

1. Use Variables

Variables are evaluated once and cached. If you reference the same calculation multiple times, using a variable avoids redundant computation:

// BAD — SUM is calculated twice
Margin =
DIVIDE(
    SUM(FactSales[TotalAmount]) - SUM(FactSales[TotalCost]),
    SUM(FactSales[TotalAmount])
)

// GOOD — SUM is calculated once
Margin =
VAR Revenue = SUM(FactSales[TotalAmount])
VAR Cost = SUM(FactSales[TotalCost])
RETURN
    DIVIDE(Revenue - Cost, Revenue)

2. Avoid FILTER on Entire Tables

// BAD — iterates every row, every column of DimProduct
Sales =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(DimProduct, DimProduct[Category] = "Electronics")
)

// GOOD — uses a simple column predicate (much faster)
Sales =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics"
)

// ACCEPTABLE — when you must use FILTER, filter a column, not a table
Sales =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(ALL(DimProduct[Category]), DimProduct[Category] = "Electronics")
)

3. Push Filters to the Storage Engine

The VertiPaq storage engine is extremely fast at filtering data. DAX formulas that let the engine handle filtering (via simple predicates) are much faster than those that force the formula engine to iterate rows.

Storage engine friendly (fast):

Simple column predicates: DimProduct[Category] = "Electronics"
ALL, VALUES, DISTINCT on columns

Formula engine required (slower):

FILTER with complex expressions
Row-by-row iteration with SUMX on large tables
Nested CALCULATE with context transition

4. Use SUMMARIZE Wisely

SUMMARIZE without extension columns is fine. SUMMARIZE with extension columns should be replaced with ADDCOLUMNS + VALUES:

// AVOID — SUMMARIZE with extension columns can give wrong results
Bad =
SUMMARIZE(
    FactSales,
    DimProduct[Category],
    "Revenue", SUM(FactSales[TotalAmount])
)

// PREFER — ADDCOLUMNS + VALUES
Good =
ADDCOLUMNS(
    VALUES(DimProduct[Category]),
    "Revenue", CALCULATE(SUM(FactSales[TotalAmount]))
)

5. Avoid Unnecessary DISTINCTCOUNT

DISTINCTCOUNT is one of the most expensive aggregation functions. If you know the values are already unique, use COUNTROWS instead:

// Expensive
Customer Count = DISTINCTCOUNT(DimCustomer[CustomerKey])

// Cheaper (if CustomerKey is the primary key)
Customer Count = COUNTROWS(DimCustomer)

6. Reduce Cardinality

High-cardinality columns (columns with many unique values) increase model size and slow queries. Consider:

Removing unnecessary high-cardinality columns (GUIDs, timestamps with seconds)
Rounding decimals to fewer places
Binning continuous values into categories

Performance Quick Reference

Tip	Impact
Use variables	Medium — avoids repeated calculation
Simple predicates over FILTER	High — uses storage engine
ADDCOLUMNS over SUMMARIZE with extensions	Medium — avoids wrong results and improves reliability
Reduce DISTINCTCOUNT usage	Medium — expensive function
Remove unnecessary columns	High — smaller model, faster queries
Avoid nested iterators on large tables	High — exponential row processing

Common Advanced Patterns

New vs Returning Customers

// A customer is "new" if their first purchase is in the current period
New Customers =
VAR CurrentPeriodCustomers =
    VALUES(FactSales[CustomerKey])
VAR PriorCustomers =
    CALCULATETABLE(
        VALUES(FactSales[CustomerKey]),
        FILTER(
            ALL(DimDate),
            DimDate[Date] < MIN(DimDate[Date])
        )
    )
RETURN
    COUNTROWS(
        EXCEPT(CurrentPeriodCustomers, PriorCustomers)
    )

Returning Customers =
VAR CurrentPeriodCustomers =
    VALUES(FactSales[CustomerKey])
VAR PriorCustomers =
    CALCULATETABLE(
        VALUES(FactSales[CustomerKey]),
        FILTER(
            ALL(DimDate),
            DimDate[Date] < MIN(DimDate[Date])
        )
    )
RETURN
    COUNTROWS(
        INTERSECT(CurrentPeriodCustomers, PriorCustomers)
    )

ABC Classification

Classify products into A (top 80% revenue), B (next 15%), C (remaining 5%):

ABC Class =
VAR ProductRevenue =
    CALCULATE(SUM(FactSales[TotalAmount]))
VAR TotalRevenue =
    CALCULATE(SUM(FactSales[TotalAmount]), ALL(DimProduct))
VAR CumulativeRevenue =
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        FILTER(
            ALL(DimProduct),
            CALCULATE(SUM(FactSales[TotalAmount])) >= ProductRevenue
        )
    )
VAR CumulativePct = DIVIDE(CumulativeRevenue, TotalRevenue)
RETURN
    SWITCH(
        TRUE(),
        CumulativePct <= 0.8, "A",
        CumulativePct <= 0.95, "B",
        "C"
    )

Parent-Child Hierarchy Flattening

Power BI doesn't natively support parent-child hierarchies well. Use the PATH functions to flatten them:

// On the Employees table (with EmployeeKey and ManagerKey):
Employee Path = PATH(Employees[EmployeeKey], Employees[ManagerKey])
// Result: "1|3|7|15" (a pipe-delimited path from root to current)

// Level in hierarchy
Hierarchy Level = PATHLENGTH(Employees[Employee Path])

// Name at each level
Level 1 = LOOKUPVALUE(
    Employees[EmployeeName],
    Employees[EmployeeKey],
    PATHITEM(Employees[Employee Path], 1, INTEGER)
)

Level 2 = LOOKUPVALUE(
    Employees[EmployeeName],
    Employees[EmployeeKey],
    PATHITEM(Employees[Employee Path], 2, INTEGER)
)

// Repeat for Level 3, Level 4, etc.

Market Basket Analysis

Find products frequently bought together:

// Step 1: Create a measure for co-occurrence count
Co-occurrence Count =
VAR SelectedProduct = SELECTEDVALUE(DimProduct[ProductName])
RETURN
CALCULATE(
    DISTINCTCOUNT(FactSales[OrderID]),
    FILTER(
        ALL(DimProduct[ProductName]),
        DimProduct[ProductName] <> SelectedProduct
    ),
    FILTER(
        FactSales,
        CALCULATE(
            COUNTROWS(
                FILTER(
                    FactSales,
                    RELATED(DimProduct[ProductName]) = SelectedProduct
                )
            )
        ) > 0
    )
)

Note: Market basket analysis in DAX is computationally expensive. For large datasets, consider performing this analysis in Python or R and importing the results.

Practice Exercises

Exercise 1: Time Intelligence

Using a sales model with DimDate and FactSales:

Create a YTD Revenue measure
Create a Same Period Last Year Revenue measure
Create a YoY Growth % measure
Create a 3-Month Moving Average Revenue measure
Create a Revenue Prior YTD measure and a YTD vs Prior YTD % measure

Exercise 2: Running Totals and Ranking

Create a Cumulative Revenue measure (running total across all time)
Create a YTD Running Total that resets each year
Create a Product Revenue Rank measure using RANKX (dense ranking, descending)
Create a Top 5 Product Revenue measure that shows total revenue from the top 5 products only
Create an Others Revenue measure (total minus top 5)

Exercise 3: Context Transition

Explain the output of each measure when placed in a matrix with DimProduct[Category] on rows:

Measure A = SUM(FactSales[TotalAmount])

Measure B =
CALCULATE(SUM(FactSales[TotalAmount]), ALL(DimProduct))

Measure C =
DIVIDE([Measure A], [Measure B])

Questions:

What does Measure A return for each row?
What does Measure B return for each row?
What does Measure C represent?
What happens to these measures at the Total row?

Exercise 4: Semi-Additive Measures

Your FactInventory table has columns: DateKey, ProductKey, WarehouseKey, QtyOnHand, UnitCost.

Create an Ending Inventory measure (quantity at the last date in context)
Create an Ending Inventory Value measure (quantity * unit cost at the last date)
Create a Beginning Inventory measure (quantity at the first date in context)
Create an Inventory Change measure (ending minus beginning)

Exercise 5: New vs Returning Customers

Build measures for customer analysis:

Total Customers: Distinct count of customers with transactions in the current period
New Customers: Customers whose first-ever purchase is in the current period
Returning Customers: Customers who purchased in the current period AND in any prior period
New Customer Revenue: Revenue from new customers only
Customer Retention Rate: Returning customers divided by total customers from the prior period

Exercise 6: Dynamic Measure Selection

Create a disconnected table called MetricSelector with values: "Revenue", "Units", "Orders", "Avg Price"
Create a measure called Dynamic KPI that returns the appropriate calculation based on the slicer selection
Create a measure called Dynamic KPI Label that returns a formatted string like "Total Revenue: $1,234,567"

Summary

In this chapter, you mastered advanced DAX concepts and patterns:

Time intelligence functions require a proper date table and enable YTD, YoY, QoQ, and MoM calculations with functions like TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, and PARALLELPERIOD
Year-over-year calculations compare current period performance to the same period in the prior year using absolute change and percentage growth
Running totals accumulate values over time using FILTER + ALL or DATESYTD
Moving averages smooth trends using DATESINPERIOD to create sliding windows of 7 days, 3 months, or 12 months
Table functions (FILTER, ALL, VALUES, ADDCOLUMNS, SUMMARIZE, TOPN, UNION, EXCEPT, INTERSECT) return tables and are used inside CALCULATE or as iterator inputs
Context transition occurs when CALCULATE converts row context into filter context — critical for understanding how measures behave inside iterators
CALCULATE deep dive covered multiple filters (AND logic), OR conditions, overriding vs intersecting filters with KEEPFILTERS, and nested CALCULATE
TREATAS creates virtual relationships without physical model changes
Dynamic segmentation uses disconnected tables and SWITCH + SELECTEDVALUE for user-driven analysis
Ranking patterns with RANKX and TOPN enable top/bottom analysis and dynamic filtering
Semi-additive measures handle inventory, balances, and snapshots using LASTDATE, LASTNONBLANK, and opening/closing balance functions
Performance optimization focuses on using variables, simple predicates over FILTER, and pushing work to the storage engine
Advanced patterns include new vs returning customers, ABC classification, parent-child hierarchy flattening, and market basket analysis

With these advanced DAX skills, you can build sophisticated analytical models that answer complex business questions. Practice these patterns with your own data to internalize them — DAX mastery comes from repetition and experimentation.