Common DAX patterns in Power BI

DAX (Data Analysis Expressions) is the formula language for Power BI semantic models. Once you've grasped the basics — filter context, row context, basic functions — the next level is reusable patterns for common analytical scenarios. Here are the patterns that come up most often.

Time intelligence

For most semantic models, time-based comparisons are the most-requested analytics. DAX time-intelligence functions (assuming you have a proper Date table marked as a date table):

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])

Sales Same Period Last Year = 
    CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

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

Sales Rolling 12 Months = 
    CALCULATE(
        [Total Sales],
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
    )

These functions require a continuous Date table — gaps in dates produce wrong results.

Period-over-period

Beyond simple YTD / YoY, arbitrary period comparison patterns:

Sales Previous Period = 
    CALCULATE(
        [Total Sales],
        DATEADD('Date'[Date], -1, MONTH)
    )

Sales Same Period 2 Years Ago = 
    CALCULATE(
        [Total Sales],
        DATEADD('Date'[Date], -2, YEAR)
    )

Ranking

Rank customers, products, regions by some measure:

Customer Rank = 
    RANKX(
        ALL('Customer'),
        [Total Sales],
        ,
        DESC
    )

Top 10 Customer Sales = 
    IF([Customer Rank] <= 10, [Total Sales], BLANK())

ALL() removes the current row's filter context so RANKX evaluates over the full set, not just the current row.

Running totals

Sales Running Total = 
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= MAX('Date'[Date])
        )
    )

Customer retention / cohort analysis

First Purchase Date = 
    CALCULATE(
        MIN('Sales'[Order Date]),
        ALLEXCEPT('Sales', 'Sales'[CustomerKey])
    )

New Customers = 
    CALCULATE(
        DISTINCTCOUNT('Sales'[CustomerKey]),
        FILTER('Sales',
            'Sales'[Order Date] = [First Purchase Date]
        )
    )

What-if scenarios

For interactive scenarios where the user picks a slider value:

1. Create a what-if parameter (Modeling tab → New parameter) with min, max, increment.
2. Reference the parameter in measures:

Sales with Discount = 
    SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Price] * (1 - [Discount %]))

Users adjust the slider; measures recalculate instantly.

Iterating with X functions

The aggregator-X functions evaluate row-by-row:

Total Revenue = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Unit Price])

Average Margin = AVERAGEX('Sales', 'Sales'[Profit] / 'Sales'[Revenue])

Use X functions when you need per-row calculations summed up — preferable to calculated columns for many cases.

CALCULATE with multiple filters

CALCULATE is the central DAX function:

EMEA Top Customer Sales = 
    CALCULATE(
        [Total Sales],
        'Customer'[Region] = "EMEA",
        'Customer'[Tier] = "Top"
    )

Each argument after the expression is a filter modifier. Filters combine with AND by default.

Variables for clarity

Long DAX expressions get unreadable. Use VAR for clarity:

Sales Margin % = 
    VAR Sales = [Total Sales]
    VAR Cost = [Total Cost]
    VAR Margin = Sales - Cost
    RETURN
    DIVIDE(Margin, Sales)

Variables are evaluated once, named for clarity. Use them liberally.

DIVIDE for safe division

Always use DIVIDE rather than /:

Conversion Rate = DIVIDE([Sales], [Visits], 0)

DIVIDE returns 0 (or your specified alternate) when the denominator is blank or zero, avoiding divide-by-zero errors.

For analysts new to DAX, these patterns cover 80% of real-world scenarios. The DAX Guide at dax.guide is the authoritative reference for every function. Practice with real models — DAX rewards experimentation.