Part of the BI Blueprint design system

DAX Quick Reference

Common DAX patterns with copy-paste formulas. Stop Googling the same syntax over and over.

Year to Date (YTD)

Time Intelligence

Calculate running total from start of year to current date.

Sales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

Quarter to Date (QTD)

Time Intelligence

Calculate running total from start of quarter.

Sales QTD = TOTALQTD(SUM(Sales[Amount]), 'Date'[Date])

Month to Date (MTD)

Time Intelligence

Calculate running total from start of month.

Sales MTD = TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

Previous Year

Time Intelligence

Compare current period to same period last year.

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

Previous Month

Time Intelligence

Get last month's value for comparison.

Sales PM = CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date]))

Year over Year Change

Time Intelligence

Calculate the difference between this year and last year.

YoY Change =
VAR CurrentYear = SUM(Sales[Amount])
VAR PreviousYear = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN CurrentYear - PreviousYear

YoY % Change

Time Intelligence

Calculate percentage growth year over year.

YoY % =
VAR CurrentYear = SUM(Sales[Amount])
VAR PreviousYear = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(CurrentYear - PreviousYear, PreviousYear)

Rolling 12 Months

Time Intelligence

Always show the last 12 months regardless of current date.

Rolling 12M =
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

Last N Days

Time Intelligence

Show last 30 days of sales.

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

Running Total

Aggregations

Cumulative sum that grows over time.

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

Distinct Count

Aggregations

Count unique values (e.g., unique customers, unique products).

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Average Ignoring Blanks

Aggregations

Average that automatically ignores blank values.

Avg Sales = AVERAGE(Sales[Amount])

Weighted Average

Aggregations

Calculate average price weighted by quantity sold.

Weighted Avg Price = DIVIDE(SUMX(Sales, Sales[Price] * Sales[Quantity]), SUM(Sales[Quantity]))

Count with Condition

Aggregations

Count rows that meet specific criteria.

High Value Orders = COUNTROWS(FILTER(Sales, Sales[Amount] > 1000))

Remove All Filters

Filtering

Get grand total regardless of any filters applied.

Total All = CALCULATE(SUM(Sales[Amount]), ALL(Sales))

Keep Only Specific Filter

Filtering

Remove all filters except one dimension.

Category Total = CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Category]))

Filter to Specific Value

Filtering

Calculate for a specific segment only.

Online Sales = CALCULATE(SUM(Sales[Amount]), Sales[Channel] = "Online")

Top N Filter

Filtering

Show total for only the top 10 items.

Top 10 Products =
CALCULATE(
    SUM(Sales[Amount]),
    TOPN(10, ALL(Products[ProductName]), [Total Sales])
)

Selected Value

Filtering

Display what's selected in a slicer, with a default if multiple/none selected.

Selected Region = SELECTEDVALUE(Geography[Region], "All Regions")

Concatenate Columns

Text

Combine text fields together.

Full Name = Customer[FirstName] & " " & Customer[LastName]

Conditional Text

Text

Return different text based on conditions.

Status Label = IF(Sales[Amount] > 1000, "High", "Low")

Replace Blank with Text

Text

Show friendly message instead of blank.

Display Value = IF(ISBLANK(Sales[Amount]), "No Data", FORMAT(Sales[Amount], "$#,##0"))

Format Number as Text

Text

Convert number to formatted text for display.

Sales Text = FORMAT(SUM(Sales[Amount]), "$#,##0.00")

Extract Text

Text

Extract portion of text (e.g., domain from email).

Domain = MID(Customer[Email], FIND("@", Customer[Email]) + 1, 100)

IF Statement

Logical

Basic conditional logic.

Status = IF([Sales] > [Target], "Above Target", "Below Target")

Nested IF / SWITCH

Logical

Multiple conditions (cleaner than nested IFs).

Rating =
SWITCH(
    TRUE(),
    [Score] >= 90, "A",
    [Score] >= 80, "B",
    [Score] >= 70, "C",
    "F"
)

Handle Divide by Zero

Logical

Safe division that returns 0 (or blank) instead of error.

Margin % = DIVIDE([Profit], [Revenue], 0)

Blank vs Zero

Logical

Convert blanks to zero for calculations.

Safe Value = IF(ISBLANK([Sales]), 0, [Sales])

AND / OR Conditions

Logical

Multiple conditions must be true.

Qualified = IF([Sales] > 1000 && [Tenure] > 1, "Yes", "No")

Basic Rank

Ranking

Rank items by a measure.

Sales Rank = RANKX(ALL(Products[ProductName]), [Total Sales],, DESC, Dense)

Rank within Category

Ranking

Rank within a group, not overall.

Category Rank =
RANKX(
    FILTER(ALL(Products), Products[Category] = MAX(Products[Category])),
    [Total Sales]
)

Percentile Rank

Ranking

Show where an item falls as a percentage.

Percentile = DIVIDE(RANKX(ALL(Products), [Total Sales], , ASC) - 1, COUNTROWS(ALL(Products)) - 1)

Pro Tips

Always use variables (VAR) for complex measures — improves readability and performance

DIVIDE() is safer than using / for division — handles zero automatically

Use CALCULATE() to change filter context — it's the most powerful DAX function

Test measures with a Card visual before using in complex charts

Put it into practice

Download the free Power BI template to see these DAX patterns in action with a complete dashboard.

Download Free Template

Free during beta • includes PBIX, theme file, and docs