Measures with CALCULATE and time intelligence

01The problem

The managing director wants three numbers on every page: revenue this year to date, growth versus the same period last year, and each category's share of revenue. None of these is a plain sum. Each needs a measure that changes the filters before calculating.

02The concept

In the last lesson, every measure accepted the filter context it was given: a Lagos cell got Lagos revenue. Many business questions need something different: Lagos revenue next to every region, each category against the total, this year against last year. For those, the measure has to change the filters before it calculates. That's what CALCULATE does, and it's the most important function in DAX.

CALCULATE

CALCULATE ( <expression>, <filter1>, <filter2>, … )

CALCULATE takes the cell's filter context, applies your changes, then evaluates the expression in the new context.

Adding or replacing a filter

Revenue Wholesale = CALCULATE ( [Revenue], customers[channel] = "Wholesale" )

Across all dates, this gives ₦580,264,905. Put it in a table by region and each row shows that region's wholesale revenue: the row's region filter stays, and CALCULATE adds the channel filter.

Put it in a table by channel, and every row shows ₦580,264,905, even the Kiosk row. A filter argument on a column replaces any existing filter on that same column. That's often exactly what you want (a fixed benchmark beside each row), but it surprises people the first time.

Several filters are combined with AND:

Revenue Lagos Beverages =
CALCULATE ( [Revenue], customers[region] = "Lagos", products[category] = "Beverages" )

That's ₦116,162,310, across all dates.

Removing filters: REMOVEFILTERS and ALL

To compare each row with a total, remove a filter instead of adding one:

Revenue All Categories = CALCULATE ( [Revenue], REMOVEFILTERS ( products[category] ) )

% of Revenue = DIVIDE ( [Revenue], [Revenue All Categories] )

In each row, Revenue is that category's revenue, and Revenue All Categories is the grand total, so the ratio is the category's share. Format % of Revenue as a percentage.

Time intelligence

DAX has functions that shift and stretch the date filter. They need the marked date table from lesson 7.

The three measures you'll use most:

Revenue YTD = TOTALYTD ( [Revenue], 'Date'[Date] )

Revenue LY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % = DIVIDE ( [Revenue] - [Revenue LY], [Revenue LY] )

• TOTALYTD adds everything from 1 January up to the latest date in the current filter. At September 2025 it shows ₦379,011,165: January to September.
• SAMEPERIODLASTYEAR shifts the dates in the filter back one year. In the cell for March 2026, it gives March 2025.
• YoY % compares them. It's blank when there's no previous year, thanks to DIVIDE.

Common measure patterns

When a measure gives a surprising number

1. Ask what's filtered. Put the measure in a table with the fields from the visual, one at a time, and watch it change.
2. Check the total row. A different total than you expected usually means a filter is being removed or replaced.
3. Test with a Card. A Card with no other fields shows the measure in the widest context: compare it with a number you know (₦830,541,245 for all revenue).
4. Break it into variables and return each one in turn to see which part is wrong.

03Example

A matrix with Date[Year] and Date[Month] in Rows:

Check the first row by hand: (48,963,925 − 37,088,460) ÷ 37,088,460 = 0.320, so 32.0%.

At month level, Revenue YTD keeps adding: it shows January alone in January, January and February in February, and so on.

04Walkthrough

1. In _Measures, add Revenue All Categories and % of Revenue. Build a table: products[category], [Revenue], [% of Revenue]. The percentages should add up to 100%.
2. Add a slicer on products[category] and pick two categories. The shares no longer add to 100%: each is a share of all four categories. Change REMOVEFILTERS to ALLSELECTED and they add to 100% again. Decide which one your report needs.
3. Add Revenue YTD, Revenue LY and YoY %. Format YoY % as a percentage with one decimal place.
4. Build a matrix with Date[Year] → Date[Month] in Rows and the three measures in Values.
5. Check one number by hand: February 2026's YoY % should equal (43.04 − 36.14) ÷ 36.14.
6. Add a Card for Revenue YTD and a slicer on Date[Month]: selecting March 2026 shows year-to-date March.
7. Add Revenue Wholesale to a table by channel and see the "replace" behaviour for yourself.

Summary

05Practice

06Challenge

07More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

08Check your understanding