DAX for Beginners: The 5 Measures Every Power BI Report Needs

Measure 3 - the running total

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

This is where DAX starts to feel different from Excel. CALCULATE changes the filter context of [Total Sales], and the FILTER inside it says "include every date up to and including the current row's date." The result: a cumulative total that builds as the report moves across days, months or years. At work it looks like a year-to-date sales line, or a project's cumulative spend against budget.

Measure 4 - the comparison to a prior period

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

SAMEPERIODLASTYEAR is one of DAX's time-intelligence functions, built specifically for this comparison. Pair it with [Total Sales] and you get last year's figure recalculated for whatever period is currently on the report, so a year-on-year comparison visual updates correctly whether someone's looking at a month, a quarter or a full year.

Measure 5 - the filtered version of a total

Sales - London Only = CALCULATE([Total Sales], Sales[Region] = "London")

Why employers care: this pattern lets you show one specific slice alongside the overall total in the same visual, without building a second report or asking someone to filter it themselves. Swap the condition for any field: a product line, a customer tier, a sales channel. Once you understand that CALCULATE overrides the current filter context with whatever you put inside it, this measure and Measure 3 are really the same technique aimed at different problems.

Common gotchas (and the fixes)

• A measure returns a number that looks wrong. Check what's providing filter context, meaning which slicers, rows or columns are active on the visual. A measure recalculates for every combination of filters on the report, so the same formula can validly return different numbers in different visuals.
• `CALCULATE` feels like magic. It is, until you learn the rule: it takes your measure and re-evaluates it inside a new filter context built from whatever conditions you list. Read it as "recalculate this, but first change these filters."
• Row context and filter context get confused. A calculated column works row by row (row context). A measure in a visual works across whatever's currently filtered (filter context). CALCULATE is the bridge between the two, which is why it appears in almost every non-trivial measure.
• Time intelligence functions return blank. They need a proper date table marked as such in Power BI (Model view, then mark as date table), with one row per day and no gaps. Skip this step and SAMEPERIODLASTYEAR and similar functions won't work reliably.

The pattern behind all five

Total, ratio, running total, prior-period comparison, filtered total: different questions, the same underlying tool. SUM or DIVIDE for the raw arithmetic, CALCULATE when you need to change what's being filtered. Once that clicks, writing a sixth or a twentieth measure is a much smaller step than writing the first five.

If your team is building reports on borrowed formulas nobody quite understands, our DAX Introduction course covers exactly this: calculated columns, measures, filter context and time intelligence, on real business scenarios over two hands-on days. Newer to Power BI altogether? Power BI Introduction builds the reporting foundation DAX sits on top of.