Power BI · Lesson 5 of 7

CALCULATE & time intelligence

Changing filter context with CALCULATE, year-to-date, last year and growth percentage.

CALCULATE is the most important DAX function. It evaluates an expression with changed filters — add a filter, replace one or remove one.

CALCULATE([Total Sales], Products[Category] = "Laptops") — sales for laptops only, whatever the slicer says about other columns.
ALL(table) removes filters — used for “percentage of total”.

Time intelligence

With a marked Date table you can compare periods easily: TOTALYTD (year to date), SAMEPERIODLASTYEAR, DATEADD (shift by months or days). Growth % = (this period − last period) ÷ last period.

Sample data & steps

Power BI Desktop (free, Windows)

Laptop Sales  = CALCULATE ( [Total Sales], Products[Category] = "Laptops" )

% of Total    = DIVIDE ( [Total Sales],
                         CALCULATE ( [Total Sales], ALL ( Sales[Region] ) ) )

Sales YTD     = TOTALYTD ( [Total Sales], 'Date'[Date] )

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

YoY Growth %  = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Matrix visual: Month | Total Sales | Sales LY | YoY Growth %

Result

Month | Total Sales | Sales LY | YoY Growth %
Sep   | 410,000     | 365,000  | 12.3%
Oct   | 452,000     | 401,000  | 12.7%
Nov   | 183,000     | 210,000  | -12.9%

Region     | Total Sales | % of Total
Kochi      | 129,000     | 70.5%
Trivandrum |  48,000     | 26.2%
Kozhikode  |   6,000     |  3.3%

Download Power BI Desktop free from Microsoft (Windows). Re-create the small sample tables in Excel, load them, and follow the steps. Data is fictional.

Key points

CALCULATE evaluates a measure with modified filters.
ALL removes filters — useful for % of total.
TOTALYTD and SAMEPERIODLASTYEAR need a marked Date table.
Growth % = DIVIDE(this − last, last).

Quick check

Which function changes the filter context of a calculation?

SUM CALCULATE RELATED FORMAT

Practice task

Create Sales MTD, Sales Previous Month (using DATEADD by -1 month) and Month-over-Month Growth %, and show them in a line and column chart.

Interview corner

What does CALCULATE do?

It evaluates an expression in a modified filter context. Its filter arguments can add conditions, override existing filters on the same columns, or remove filters with ALL/REMOVEFILTERS. Most advanced DAX is built on it.

What is filter context versus row context?

Filter context is the set of filters applied to a calculation by slicers, visuals and CALCULATE. Row context is the current row when DAX iterates a table, as in calculated columns or iterator functions like SUMX.