The CALCULATE function evaluates an expression in a context that is modified by the filters that are included as arguments.
CALCULATE(
expression,
filter1,
filter2...
)
- expression: Expression to evaluate.
- filter1, filter2...: Optional arguments. List of Boolean or table expressions that define filters to apply to expression.
The CALCULATE function returns the value defined by expression after modifying the context according to the filters included as arguments.
If the expression to be evaluated is of the Boolean type, it cannot refer to a measure, nor use the nested CALCULATE function, nor use iterative functions (to scan a table) nor functions that return a table.
For each column that is included in the function's arguments, any filters that exist on that column will be ignored and the filter represented in the argument will be applied instead. This means that those filters imposed by the context that contradict the CALCULATE filters will be ignored.
In the case of having more than one filter in the function, they will be applied independently to the data. That is, a logical "AND" is applied to them so that a row of the source data must meet all the filters to be considered in the calculation. In the case that a logical "OR" needs to be applied to the filters, the operator || can be used. :
...in which case a row will be considered in the calculation when it satisfies the condition imposed by filter1 or when it satisfies the one imposed by filter2. In this case, the filters must evaluate the same column: it is not possible for one filter to impose a condition by evaluating one column and for the second filter to impose a condition by evaluating a different column.
When you want to add a complex comparison as a filter (of the type [column] = [measure], [column] = <formula>, [column] = [column], etc.) -expressions that are not allowed as arguments in CALCULATE- the FILTER function must be used.
If we define the Ventas measure as the sum of the SalesAmount column:
Ventas = SUM(FactSales[SalesAmount])
...we can create a new measure based on the CALCULATE function that modifies the calculation context so that it only considers sales for the year 2008:
Ventas 2008 = CALCULATE([Ventas], DimDate[CalendarYear]=2008)
The following image shows a visualization (in the upper part) of the "stacked column chart" type in which the Ventas measure has been taken to the Value field and the Datekey field to the Axis field of the visualization. A "card" type visualization is shown below with the Ventas 2008 measure and, finally, a visualization similar to the first one is created but taking the Ventas 2008 measure to the Value field. As can be seen, only data relating to the year 2008 is shown:
If we want to calculate sales before a certain date, we can use the CALCULATE function by adding the appropriate filter. To show this example, we define the following measure:
[Ventas],
FactSales[DateKey]<=DATE(2007,1,2)
)
...which adds as a filter the condition that the sale date is before or equal to January 2, 2007. The following image shows a table-format display of sales ordered by day, and a card-type display with the Ventas anteriores measure:
It can be seen how the result of the measure coincides with the sum of the sales on January 1 and 2.
If we wanted to calculate sales between two dates, for example between January 3 and 4 (both included), we could define the following measure:
Ventas anteriores = CALCULATE(
[Ventas],
FactSales[DateKey]>=DATE(2007,1,3),
FactSales[DateKey]<=DATE(2007,1,4)
)
...resulting:
The resulting sum coincides with that of the sales of January 3 and 4.
If the CALCULATE function receives a table as a filter argument, the expression will be evaluated only for the data contained in the table. For example, if we use the DATESBETWEEN function to extract a single column table containing a set of dates, the expression will be evaluated only for those dates:
Ventas anteriores = CALCULATE(
[Ventas],
DATESBETWEEN(
FactSales[DateKey],
DATE(2007,1,3),
DATE(2007,1,5)
)
)
- Calculation of accumulated totals
- Growth compared to a previous period
- Analysis of the evolution of sales with a window of 14 days
- Calculation of total sales by category regardless of the selected country
- Calculation of sales prior to a given date
- TOTALYTD function emulation
- Range of locations according to the number of units sold
- Calculation of the previous purchase date for each purchase of each customer
- Get the last month of a calendar
- Calculation of an expression only if the values involved are not null
