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

CALCULATE evaluates an expression under modified filters:

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

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

That measure shows Lagos revenue even in a visual sliced by another region: the filter argument replaces the region filter.

Removing filters: ALL / REMOVEFILTERS

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

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

In a table by category, the first measure ignores the category on each row, giving the grand total, so the ratio is each category's share. Format % of Revenue as a percentage.

Time intelligence (needs the marked date table from lesson 7):

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.
• SAMEPERIODLASTYEAR shifts the dates in the filter back one year.
• YoY % compares them; blank when there's no previous year.

03Example

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

And Revenue YTD at 2026 March shows Q1 2026 in total.

04Walkthrough

1. In _Measures, add Revenue All Categories and % of Revenue. Build a table: products[category], [Revenue], [% of Revenue].
2. Add Revenue YTD, Revenue LY and YoY %. Format YoY % as a percentage with one decimal place.
3. Build a matrix with Date[Year] → Date[Month] in Rows and the three measures in Values.
4. Check one number by hand: February 2026's YoY % should equal (43.04 − 36.14) ÷ 36.14.
5. Add a Card for Revenue YTD and a slicer on Date[Month]: selecting March 2026 shows year-to-date March.

05Practice

06Challenge

07Check your understanding