The CALCULATE() function is a powerful function used to manipulate the filter context. The first argument takes an expression or a measure (a measure is just a named expression). Subsequent arguments allow modifying the filter context.
Total Sales for 2015 =
CALCULATE(
SUM('Sales OrderDetails'[Total Price]),
YEAR('Sales OrderDetails'[orderdate]) = 2015
)
Sales by Ship Date =
CALCULATE(
Sum(Sales[TotalPrice]),
USERELATIONSHIP(Sales[ShipDate],'Calendar'[Date])
)
Last Inventory Count =
CALCULATE (
SUM ( 'Warehouse'[Inventory Count] ),
LASTDATE ( 'Date'[Date] )
)
Sales All Region =
CALCULATE(
SUM(Sales[Sales]),
REMOVEFILTERS(Region) //The REMOVEFILTERS() function removes active filters. It can take either no arguments, or a table, a column, or multiple columns as its argument.
)
Sales % All Region =
DIVIDE(
SUM(Sales[Sales]),
CALCULATE(
SUM(Sales[Sales]),
REMOVEFILTERS(Region)
)
)
The IF() function uses the ISINSCOPE() function to test whether the region column is the level in a hierarchy of levels. When true, the DIVIDE() function is evaluated. When false, a blank value is returned because the region column isn’t in scope.
Sales % Country =
IF(
ISINSCOPE(Region[Region]),
DIVIDE(
SUM(Sales[Sales]),
CALCULATE(
SUM(Sales[Sales]),
REMOVEFILTERS(Region[Region])
)
)
)
Sales % Group =
IF(
ISINSCOPE(Region[Region]) || ISINSCOPE(Region[Country]),
DIVIDE(
SUM(Sales[Sales]),
CALCULATE(
SUM(Sales[Sales]),
REMOVEFILTERS(
Region[Region],
Region[Country]
)
)
)
)
Some time intelligence examples:
YTD Total Sales =
TOTALYTD (
SUM('Sales OrderDetails'[Total Price])
, Dates[Date]
)
Sales YTD =
TOTALYTD(
SUM(Sales[Sales]),
'Date'[Date],
"6-30" //TOTALYTD function can also take a third optional argument representing the last date of a year. The absence of this argument in example above means that December 31 is the last date of the year. This example specifies that June is the last month of their year, and so “6-30” is used.
)
Total Sales Previous Month =
CALCULATE (
sum('Sales OrderDetails'[Total Price])
, PREVIOUSMONTH(Dates[Date])
)
//calculate Year-over-Year growth
//The SalesPriorYear variable is assigned an expression that calculates the sum of the Sales column in a modified context that uses the PARALLELPERIOD() function to shift 12 months back from each date in filter context.
Sales YoY Growth =
VAR SalesPriorYear =
CALCULATE(
SUM(Sales[Sales]),
PARALLELPERIOD(
'Date'[Date],
-12,
MONTH
)
)
RETURN
DIVIDE(
(SUM(Sales[Sales]) - SalesPriorYear),
SalesPriorYear
)
Understand filter and nested CALCULATE(). Following expression, “$$ in WA” and “$$ in WA !!”, “$$ in WA and OR” and “$$ in WA and OR ??” return the same results respectivly (see KEEPFILTERS())
EVALUATE ROW(
"$$ in WA"
, CALCULATE('Internet Sales'[Internet Total Sales]
, 'Geography'[State Province Code]="WA"
)
, "$$ in WA and OR"
, CALCULATE('Internet Sales'[Internet Total Sales]
, 'Geography'[State Province Code]="WA"
|| 'Geography'[State Province Code]="OR"
)
, "$$ in WA and BC"
, CALCULATE('Internet Sales'[Internet Total Sales]
, 'Geography'[State Province Code]="WA"
|| 'Geography'[State Province Code]="BC"
)
, "$$ in WA and OR ??"
, CALCULATE(
CALCULATE('Internet Sales'[Internet Total Sales]
,'Geography'[State Province Code]="WA"
|| 'Geography'[State Province Code]="OR"
)
, 'Geography'[State Province Code]="WA"
|| 'Geography'[State Province Code]="BC"
)
, "$$ in WA !!"
, CALCULATE(
CALCULATE('Internet Sales'[Internet Total Sales]
, KEEPFILTERS('Geography'[State Province Code]="WA"
|| 'Geography'[State Province Code]="OR"
)
)
, 'Geography'[State Province Code]="WA"
|| 'Geography'[State Province Code]="BC"
)
)
For more details, see Microsoft Learn and Microsoft Learn lab