10 DAX Formulas Every Business Analyst Should Know

DAX (Data Analysis Expressions) is the formula language of Power BI, Power Pivot, and Analysis Services. Mastering it is the single skill that separates a Power BI analyst who can build basic reports from one who can answer any business question with data. These 10 DAX patterns cover 80% of the business calculations you will encounter in real enterprise deployments — with working code you can adapt immediately.

What Is DAX?

DAX is a functional language where formulas evaluate in the context of rows, columns, tables, and filters. Every DAX measure evaluates dynamically based on whatever slicers and filters are active in the report — this is called filter context. Understanding filter context is the key to writing DAX correctly.

DAX has two types of calculations: calculated columns (evaluated row by row during data load, stored in the model) and measures (evaluated on demand based on the current filter context). For performance, prefer measures over calculated columns wherever possible.

1. CALCULATE — The Most Important DAX Function

CALCULATE evaluates an expression in a modified filter context. It is the foundation of almost every advanced DAX pattern.

-- Sales in the East Region only (regardless of region slicer)
East Region Sales =
CALCULATE (
    SUM ( Sales[Amount] ),
    Region[RegionName] = "East"
)

-- Sales for the current year to date
Sales YTD =
CALCULATE (
    SUM ( Sales[Amount] ),
    DATESYTD ( 'Date'[Date] )
)

The second argument to CALCULATE is a filter — it replaces or modifies the existing filter context for that expression. This is how you override what a slicer is doing and compute "Sales for the East, no matter what region the user selected."

2. Year-Over-Year Growth

Year-over-year comparison is one of the most common business metrics. Use SAMEPERIODLASTYEAR with CALCULATE:

Sales LY =
CALCULATE (
    SUM ( Sales[Amount] ),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Sales YoY Growth % =
DIVIDE (
    SUM ( Sales[Amount] ) - [Sales LY],
    [Sales LY],
    0
)

Always use DIVIDE instead of the / operator — DIVIDE handles division by zero gracefully, returning the third argument (0 in this case) instead of an error.

3. Running Total (Cumulative Sum)

Cumulative sales, cumulative production, or cumulative expenses over a period:

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

The ALL() function removes any existing date filters, then the FILTER adds back only dates up to the current maximum date — creating a cumulative sum that resets correctly when filtered by year or month.

4. Moving Average

A 3-month or 12-month moving average smooths seasonal volatility and reveals underlying trends:

Sales 3M Moving Average =
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR StartDate = DATEADD ( 'Date'[Date], -2, MONTH )
RETURN
CALCULATE (
    AVERAGEX (
        VALUES ( 'Date'[MonthYear] ),
        SUM ( Sales[Amount] )
    ),
    DATESBETWEEN ( 'Date'[Date], StartDate, CurrentDate )
)

5. Ranking

Rank products, customers, or branches by a measure — with RANKX:

Product Sales Rank =
RANKX (
    ALL ( Products[ProductName] ),
    SUM ( Sales[Amount] ),
    ,
    DESC,
    Dense
)

The ALL() in the first argument tells RANKX to rank across all products, not just those visible in the current filter context. Dense ranking means tied values get the same rank with no gaps (1, 1, 2, 3 instead of 1, 1, 3, 4).

6. Percentage of Total

Show each product or region's share of total sales:

Sales % of Total =
DIVIDE (
    SUM ( Sales[Amount] ),
    CALCULATE ( SUM ( Sales[Amount] ), ALL ( Sales ) ),
    0
)

ALL(Sales) removes all filters from the Sales table — giving you the grand total in the denominator regardless of which product or region is currently in context.

7. Previous Period Comparison with VAR

Using VAR (variables) makes complex DAX readable and debuggable:

Sales MoM Change =
VAR CurrentMonthSales = SUM ( Sales[Amount] )
VAR PreviousMonthSales =
    CALCULATE (
        SUM ( Sales[Amount] ),
        PREVIOUSMONTH ( 'Date'[Date] )
    )
VAR Change = CurrentMonthSales - PreviousMonthSales
RETURN
IF (
    ISBLANK ( PreviousMonthSales ),
    BLANK (),
    DIVIDE ( Change, PreviousMonthSales, 0 )
)

Using VAR improves performance (each expression is evaluated once and stored) and makes it easy to debug — you can return any intermediate VAR value to check it.

8. Dynamic Segmentation with SWITCH

Segment customers into tiers (Bronze / Silver / Gold) based on purchase value:

Customer Tier =
VAR TotalSales = CALCULATE ( SUM ( Sales[Amount] ), ALL ( 'Date' ) )
RETURN
SWITCH (
    TRUE (),
    TotalSales >= 1000000, "Gold",
    TotalSales >= 500000, "Silver",
    TotalSales >= 100000, "Bronze",
    "Standard"
)

SWITCH(TRUE(), ...) is the DAX equivalent of a multi-condition IF statement. It evaluates each condition from top to bottom and returns the first match.

9. Time Intelligence — Last 30 Days

Rolling 30-day windows are essential for operational KPIs that should not be constrained by calendar month boundaries:

Sales Last 30 Days =
CALCULATE (
    SUM ( Sales[Amount] ),
    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        -30,
        DAY
    )
)

10. Conditional Formatting Measure

Return a hex colour code based on a KPI value — for conditional formatting in table and matrix visuals:

KPI Colour =
VAR Value = [Sales Growth %]
RETURN
SWITCH (
    TRUE (),
    Value >= 0.1, "#22C55E",   -- Green: 10%+ growth
    Value >= 0,   "#F59E0B",   -- Amber: 0–10% growth
    "#EF4444"                  -- Red: negative growth
)

In Power BI, set the visual's conditional formatting to "Field value" and select this measure as the source. The visual will then colour each row or cell dynamically based on its actual value.

DAX Best Practices

• Always use a Date table: Mark a dedicated Date table with MARK AS DATE TABLE. All time intelligence functions require a proper date table to work correctly.
• Use measures, not calculated columns: Calculated columns consume model memory for every row. Measures compute on demand and scale better.
• Use DIVIDE, not /: DIVIDE handles division-by-zero gracefully. The / operator throws an error.
• Use VAR for complex expressions: Variables improve readability, debuggability, and performance by avoiding expression re-evaluation.
• Avoid FILTER on large tables: FILTER(ALL(BigTable), ...) iterates every row and is slow. Use CALCULATE with column-level filter arguments where possible.