ABC of Power BI: “C” is for CALCULATE
Hugh Johnson explores 10 things that accountants should know about the most powerful DAX function.
Alberto Ferrari, one of the world’s most famous experts on Power BI, compares CALCULATE in DAX to the queen on a chessboard; it is extremely powerful and you can learn its basic capabilities very quickly. Unlike the queen, though, CALCULATE goes way beyond what you first see, so much so that Alberto Ferrari and Marco Russo dedicate 35 pages to it in their book “The Definitive Guide to DAX”.
Why is CALCULATE such an important function for accountants?
The numerical part of a financial report comprises transactional data that are filtered and summarised by ledger codes, periods, quarters and years. Power BI filters and aggregates data very naturally, just like a Pivot Chart in Excel.
For any half-decent financial reporting, though, you will need to write some DAX expressions and this will inevitably lead to CALCULATE. For an accountant, the most common scenario will be to manipulate date filters. Let me explain one very simple case.
Let’s say that you have a simple Power BI model that has a transactions table where Transactions[NetAmount] is the net P&L amount of every single transaction over the last few years and that you have a related NominalRecords table. Your model might look something like this:
Using the natural filtering and summarisation that comes with Power BI, you may be able to create a monthly Profit & Loss report like the one below without writing any code; just drag and drop:
In this report, I have used the slicers to select Month = Feb and Fiscal Year (FY) = 2019 and the page presents a P&L report for Feb, FY 2019. Power BI generates all of the charts on the fly as it summarises the Transactions[NetAmount] value by Nominal code and group, Department, Fiscal Year and Month. It is the filter context in Power BI that determines what data are visible to these summarisations.
This is fine if you just want to present the P&L by month as in the example above. What, though, if I want to show YTD? Well, I suppose you could select each one of the 11 Months from April to Feb, but that is not very user-friendly. If you wanted to show something more advanced, like year-on-year variance, then you have no choice but to start writing some DAX expressions.
Let’s look more closely at the YTD problem. In the example, I have selected “Feb” and “2019”. This means that the only transactions visible from my dataset are now just those dated Feb, FY 2019. With only these data, Power BI cannot compute values for YTD. We need somehow to acknowledge that I have selected Feb, FY 2019, and then modify the filter context so that the summarisation sees transactions for all of the 11 months in Fiscal Year 2019, up to and including Feb 2019.
This is where CALCULATE comes in. The main use of CALCULATE is to modify the Power BI filter context for an expression. This enables you to create all kinds of date-driven analysis, such as YTD, PYTD, PY, PM and is why it is so important for you, as an accountant, to get to grips with CALCULATE.
The good news is that, despite its many subtleties, you can pick up the basic use of CALCULATE quickly and easily. I do not intend this article to be a tutorial on CALCULATE, rather a summary or reminder of its capabilities. For a tutorial, I would recommend that you go to one of the resources listed at the bottom under “Useful Links”.
10 things that you should know about CALCULATE
The name “CALCULATE” is a false friend
Just as “sensible” in Spanish does not mean “sensible” in English, “CALCULATE” in DAX does not mean “calculate” in English. Its real function is to modify the context under which an expression is evaluated. In other words, CALCULATE modifies the data that are visible to a calculation. This is not the same thing as doing a calculation.
CALCULATE Syntax and basic use
For such a powerful function, the DAX syntax for CALCULATE is really simple. It is just:
CALCULATE ( <expression>, <filter1>, <filter2>… )
The <expression> is any valid DAX expression (such as SUM(Transactions[NetAmount]), after which any number of filters can be added. These filters modify the filter context for the <expression> for specified tables and columns.
In our example, filter context for the Dates table is FY = 2019 and Month = Feb. This means that the only transactions that the visualisations will see will be 1st – 28th Feb, FY 2019.
To calculate a YTD value, based on the slicer selection in the image, we must modify this filter context to give “All and only the dates in FY 2019 up to and including 28th Feb”. We can use CALCULATE to do this in a [PLYTD] measure like the following:
In this case, we have a generic Profit & Loss measure called [PL] (line 6), that we “wrap” with a modified filter context that is the logical AND of the following three filters:
ALL ( Dates ) (line 7) removes all existing filters on the Dates table from the report, slicers and other visualisations.
Dates[FiscalYear] = SelectedFiscalYear ( line 8) means that the Dates[FiscalYear] column is filtered only to include the SelectedFiscalYear value that is declared as a variable in line 2. In our example, this would be 2019 as selected in the slicer.
Dates[Date] <= MaxDate in line 9 means that my Dates[Date] column (and therefore the range of visible dates) is filtered only to include dates that are less than or equal to the MaxDate value that is declared as a variable in line 3. In our example, this would be 28th February, 2019 (because of the slicer selections FY = 2019 and Month = “Feb”).
Applying all of these three filters together in a logical AND will present to the generic [PL] measure a Dates table that only includes the set of dates, in this case, for the Fiscal Year 2019, up to and including 28th Feb.
CALCULATE has modified the filter context of the Dates table, but just for this measure [PLYTD]. No other filters (such as Nominal Code or Department) are affected nor any other measures (unless they themselves call [PLYTD]).
CALCULATE and Time Intelligence
Some of you may be thinking “Why should I use CALCULATE to create a YTD measure, when there is a function called TOTALYTD that is designed specifically for this purpose?”
Using TOTALYTD, I could rewrite my earlier PLYTD measure in much fewer lines as shown below:
In line 5, “31/03” specifies the year-end date.
This looks very appealing at first sight. TOTALYTD is one of many “Time Intelligence” functions that aim to help you to filter dates. These Time Intelligence functions can give you some issues though:
Some Time Intelligence functions have an optional filter, but just one. This is very limiting compared with CALCULATE that can have an unlimited number of filters.
Some Time Intelligence functions, like TOTALYTD, include an optional year-end-date argument so that you can handle fiscal years that don’t align with the calendar year. This is useful, but potentially dangerous, as it introduces a code maintenance problem if you want to use the same report for many companies with different year-end dates. Instead, I prefer to use a dynamic Dates table that picks up the year-end-month from a single measure, field or parameter in the report.
A more subtle problem, as Marco Russo points out in this blog, is that since the year end date is an optional argument, it can be confusing as to whether the third argument is a filter or a value that represents the year end date. This can create situation where TOTALYTD looks like it is working correctly, but is actually not giving the result that you want.
Time Intelligence functions require that the <dates> argument needs to be a set of contiguous dates that fully encompass the period being analysed. If you are using the date column from a validated date table, and these dates include any date referenced by the time intelligence function, then you will be ok. There are many times, though, when I want use other tables to drive my date filters for measures. In these cases I cannot use Time Intelligence functions even if I wanted to. I can use CALCULATE, though.
All of this suggests that you can get by very well knowing how to use CALCULATE and not knowing how to use many of the Time Intelligence functions, but not the other way around.
Even if it doesn’t look like it, all CALCULATE filters are all table filters.
When I first started working with CALCULATE, I often got the error message (or similar) that you can see in the image below:
I didn’t appreciate that under the hood, all CALCULATE filters are table filters.
My confusion crept in, because filters like Dates[Date] <= MaxDate in line 7 do not look like table filters. Line 7 is actually shorthand that is nice to read, but behind the scenes is really the following:
FILTER ( ALL ( Dates[Date] ), Dates[Date] <= MaxDate )
As it happens, Power BI is able to correctly interpret my shorthand Dates[Date] <= MaxDate, and there is no problem.
For the DATEADD function in lines 8 to 12 there is a problem. I have tried to write the shorthand version and Power BI gives an error. If you look at the documentation for DATEADD you can see that the return value is a table. It has no meaning to apply a “>” operator to a table and I get an error.
In this case, I need to write the full version of the filter that would be:
FILTER ( ALL ( Dates[Date], DATEADD ( Dates[Date], 1, YEAR ) > MaxDate )
This works, because even though DATEADD is still returning a table, FILTER is an iterating function and for each iteration it returns a single row, single column table (i.e. a single date value).
Since both of the CALCULATE filters are operating on the Dates[Date] column, I can combine them into a single long-hand version as shown below:
My understanding is that this long-hand version using FILTER, is no less efficient for Power BI to execute. It just takes more lines to write.
As many filters as you want
CALCULATE will accept as many filters as you want; as long as they satisfy the conditions in point 4 above. This gives you tremendous flexibility.
All the CALCULATE filters are applied in a logical AND to your expression
It doesn’t matter in what order you write your filters. They are applied together, not sequentially, in a logical AND. This very simple rule means that as long as you can define an upper bound as one filter and a lower bound as another, then you can use CALCULATE to modify the filter context to any contiguous date range with two filters.
To illustrate using the two date filters, let’s revisit the example of the rolling 12 months measure in point 4 above. I find that it helps to imagine (or sketch) this as a kind of hybrid Venn diagram/number line like shown below:
Each filter only modifies the part of the filter context specified by that filter
Sometimes you may look at a report visualisation, expecting to see something, and all you see is a blank visual, like below:
Almost certainly the problem in this case is that the filter context for this visual is not what you are expecting. The first thing to do is to check the little filter icon in the top right corner of the visual (when it is selected like shown). This will show you what Report, Page and Visual level filters are active. Next, check your slicers and also make sure that you have no selections in any of the other visuals. If these are all correct, then the next most likely scenario is that the filters in CALCULATE are conflicting with each other or with the filter context.
It might be obvious, but it is important to understand, that each filter argument only modifies the part of the filter context that is specified by that argument.
A CALCULATE filter argument “NominalRecords[IsProfitAndLossAccount] = TRUE()” will conflict with another filter “NominalRecords[Group] = “Current Assets” and produce a null result.
The filters are evaluated before the <expression>
As explained earlier, the syntax for CALCULATE is as follows:
The filters modify the filter context under which the <expression> will be evaluated. Therefore the filters are evaluated first, then the expression.
So far so good, but please note that the <expression> may itself use CALCULATE. This could be explicit in the sense that you can see CALCULATE ( CALCULATE ( <expression>, filter1, filter 2 etc.), filter1, filter2 etc.), or it may be less obvious such as in our PLYTD measure:
What I didn’t tell you earlier, was that my generic [PL] measure is also created using CALCULATE:
In this inner measure [PL], there is a filter on the NominalRecords table for NominalRecords[IsProfitAndLoss] = TRUE().
This filter will be evaluated after the three filters on Dates, Dates[FiscalYear] and Dates[Date], but before SUM(Transactions[NetAmtForProfitAndLoss]). In this case, we don’t need to be concerned because the inner CALCULATE function is acting on a different table. It would matter, however, if the inner CALCULATE function had a filter on Dates, Dates[FiscalYear] or Dates[Date]. In this case, the inner filter would override the outer filter.
I have prepared a short video with an example showing how nested CALCULATE functions can cause issues if you are not careful.
Change the active relationships in your model with USERELATIONSHIP
CALCULATE modifies the filter context for an expression. An important driver for the filter context is the relationship between two tables. In the relationship diagram below, the Transactions table is filtered by the Dates and NominalRecords tables.
You can see in the diagram that there are two relationships between Dates and Transactions. One is active (with a solid line) and the other is inactive (with a dotted line). The active relationship is between Dates[Date] and Transactions[Date. The inactive relationship is between Dates[Date] and Transactions[DueDate].
By default, the active relationship drives the filter context. Sometimes, though, you may want to modify the filter context so that it is driven by the inactive relationship. You can use CALCULATE with an additional function USERELATIONSHIP to do this.
I can illustrate this with a simple example. In the measure below, I am calculating the outstanding amount of all sales invoices with a due date of last month:
Dates[RelativeMonth] is a column in my Dates table where -1 is last month.
Transactions[Type] = “SI” filters the Transactions table to contain just sales invoices.
USERELATIONSHIP switches the relationship, just for this measure, between the Dates table and the Transactions table to the DueDate of each transaction. Without this (lines 4-7 in the image), the measure would calculate the outstanding invoices that were issued last month (based on the transaction date) and not with a due date of last month.
Switch a Row Context to a Filter Context for Calculated Columns
When you use CALCULATE to create calculated columns in your dataset, the results might surprise you until you appreciate that CALCULATE will change the row context in a calculated column to a filter context. You can find a good explanation of the row context and filter context in this video by Alberto Ferrari.
I have also prepared two much shorter videos on context transition that give you a quick overview.
By default, unless you use the RELATED, RELATEDTABLE, or CALCULATE functions, a calculated column expression cannot see any data outside the table you are working on. In fact, unless you are using an aggregation function such as SUM, your expression will be evaluated row by row, and the only data visible for each iteration will be the data in that row. In its simplest form, you can (whether you should or not is a different matter) create a calculated column like this, that multiplies the net amount by 1.15:
NewColumn = Transactions[NetAmount] * 1.15
In this example, there is only one piece of data visible for each iteration of the calculation and that is Transactions[NetAmount] for that particular row.
Now, if I wanted to create a column that multiplied the net amount by 1.15 but only where the Nominal Record Group is “Sales”, then I can use CALCULATE to “switch on” the filter context and use the relationship that exists between NominalRecords and Transactions. I could create a calculated column like the one below.
CALCULATE has done a couple of things here. Firstly, it has made the relationship visible and active between NominalRecords and Transactions. Secondly, despite SUM being an aggregation function, it has evaluated Transactions[NetAmount] within the filter context at row level.
You can see this second effect more starkly in the following example. In the Transactions table, I have added two new calculated columns; ColumnSUM and ColumnCALCULATESUM. I have also added a measure MeasureSUM.
In all cases, the calculation SUM ( Transactions[NetAmount] ) is doing the same thing, just on different data, because of the different contexts.
In the case of ColumnSUM, since it is a calculated column there is a row context present, but SUM is an aggregation function that ignores this and sees all of the rows in the column to produce the same result for every row.
In the case of ColumnCALCULATESUM, the row context of the calculated column is transformed to a filter context. At row level, within a filter context, the only data visible are in that row (further filtered by any table relationships). Hence ColumnCALCULATESUM returns a (potentially) different value for each row.
The case of MeasureSUM is identical to the case of ColumnCALCULATESUM, except for when the evaluation takes place. A measure is evaluated at the time that Power BI displays it in a visual. A calculated column is evaluated at the time that you open your Power BI report. What is not obvious, but it happens, is that there is an implied CALCULATE wrapped around every measure that you write, which means that all measures are evaluated under a filter context.
If you want to use Power BI to summarise financial data over anything other than complete calendar years, quarters, months or days, then CALCULATE will be your best friend. It is very easy to start using, but has some subtleties that you should be aware of that are very powerful, but can also be the source of some unexpected results.
To use CALCULATE, you really do need to have an understanding of the Filter Context, which is quite a straightforward, but very important, concept. CALCULATE modifies the filter context for an expression and, with a little imagination and practice, you never need to fear writing date filters again. It has a very simple, logical syntax that is easy to learn and start to use. If you use CALCULATE as a simple filter for your measures and you do not have any nested CALCULATE functions (see point 8 above) then you should not experience any surprises.
There are less obvious, yet powerful features of CALCULATE that come to light if you are working with calculated columns or if you have nested CALCULATE functions, and it is easy to have nested CALCULATED columns without realising it. You can even use CALCULATE to modify the relationships in your model for the purposes of a single calculation.
You might also be interested in
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...