Cumulative sum in Power BI: CALCULATE, FILTER and ALL

While working on a Power BI report, I found myself in need of a measure for showing a cumulative sum in one of my visuals. While completing this task, I learned some important notions regarding Power BI and the creation of powerful DAX measures. In this article, I will try to show you how flexible Power BI can really be when designing new measures for your reports.

Let’s assume that our objective is to create a graph like the one represented in the image below: a regular bar chart showing a monthly revenue, only including an additional line to show a cumulative sum over the the x-axis.

The dark blue line tracks a cumulative sum throughout the different months of the x-axis, from Jan to Oct.

Looking around for helpful insights, I came across a widely accepted solution based upon three fundamental DAX functions: CALCULATE, FILTER and ALL.
The formula is usually defined as follows:

Cumulative Sum Example =
CALCULATE(
  SUM(Sales[Revenue]),
  FILTER(
    ALL(Sales),
    Sales[SaleDate] <= MAX(Sales[SaleDate])
  )
)

In order to properly analyse this formula, firstly we need to setup a simple test scenario; let’s assume that our model is composed just by two tables, Calendar and Sales, connected to each other via a one-to-many relationship over the columns Calendar[Date] and Sales[SaleDate]:

A simple test model: the two tables are connected thanks to the respective Dates columns.

By defining a relationship between these two tables, any filter applied to the Calendar[Date] column will automatically propagate to the Sales[SaleDate] column. For example, if we decide to filter our Calendar only to show the dates from October 2022, our Sales table will also show only the Sales dated in October 2022 (which will usually be more than just 31 occurrences, of course). The line connecting the two tables, shown in Power BI model view, defines the flow of the filters shared between the tables.

Before fully grasping the inner mechanisms of our cumulative sum formula, one last notion you should know about is the definition of the filter context.
The filter context is the overall group of filters that define which portions of our tables will be considered when a measure is evaluated. Most commonly, Power BI Users will modify the filter context by operating directly on the UI, while adding, changing or removing one or more filters on a visual, page and/or report level. Additionally, the filter context can be modified also by the DAX function CALCULATE: in each of our measures, we can dynamically change our filter context depending on our reporting needs (as we’re going to see for the cumulative sum formula).

So, after taking everything in, let’s go back to our measure and let’s analyse it step-by-step:

1. The function CALCULATE is called:

CALCULATE(
  operation_to_perform,
  filters
)

As already mentioned, this function will allow us to modify the filter context into which our chosen operation will take place. So, inside the CALCULATE, we can decide not only which operation to perform, but also if we want to keep, change or remove the current filter context.

2. Since we are interested in the cumulative sum of our Revenues, we simply define a sum operation on that column as the heart of our CALCULATE:

CALCULATE(
  SUM(Sales[Revenue]),
  filters
)

Depending on your needs, you could also use other functions (like a COUNT function, for example, if your feature of interest is not suitable for a sum operation). You will soon understand that you have a great degree of flexibility in this regard, and that you can use CALCULATE whenever you need not only to perform a specific operation, but also when you need to have full control over the filter context in which this operation will be executed.

3. As the second parameter of our CALCULATE, we use a FILTER function:

CALCULATE(
  SUM(Sales[Revenue]),
  FILTER(
    table,
    filter_expression
  )
)

As parameters of our FILTER, we need to specify the table we want to consider (or a function returning one, as we will see) and an overall logical expression indicating how we want to change the filters of the considered table. Based on this function’s signature, we then define our measure as:

CALCULATE(
  SUM(Sales[Revenue]),
  FILTER(
    ALL(Sales),
    Sales[SaleDate] <= MAX(Sales[SaleDate])
  )
)

The content of the FILTER function is probably the most complex part of the measure: once you managed to understand this aspect, everything else will fall into place accordingly.
For starters, we know that as its first parameter the FILTER function takes a table, or any function returning one: ALL is one of these functions.
ALL takes as input a table (or even just one or more columns) and removes any filter present on it; if we look at our measure, ALL(Sales) will remove any filter from our Sales table coming from the original filter context.
To understand which filters would be present in the filter context at the time of evaluation of our measure, keep in mind that whenever we have a bar chart and we set on the x-axis a column from our Calendar table (e.g. the Month column), Power BI will cycle through each month and iteratively filter our Calendar[Month] column, consequently filtering also the Calendar[Date] column and, thanks to the relationship between our tables, the Sales[SaleDate] column.

Schematised view of the filter flow imposed by Power BI when calculating a single month of the bar chart.

As an example, let’s assume that Power BI is considering the data from the month of October, or, in more laymen terms, that Power BI is now creating the column corresponding to October in our bar chart. When evaluating our measure, the starting filter context will contain a filter over our Calendar table, in order to consider only the dates of the month of October: this sort of filter is implicitly imposed by Power BI when looping trough each element of our x-axis. Thanks to the relationship between our tables, this filter is also propagated to our Sales table, so that only the rows with SalesDate in October will be taken into consideration. When using the ALL function, we are basically telling Power BI to undo this process and to consider the entirety of our Sales table, as if this original filter coming from the Calendar table was never applied. Since our final objective is to have a cumulative sum for each month, we indeed need to consider all the data coming also from the previous months, not just the current one.

If the ALL function removes all of the filters from our Sales table, you may think that the second parameter of the FILTER function
Sales[SaleDate] <= MAX(Sales[SaleDate])is not really significant: since ALL has removed all of the pre-existing filters, aren’t we just saying Power BI to consider all the rows of the Sales table with a SaleDate earlier or equal than the maximum possible SaleDate? Here, the key point to understand is that our MAX operation will take place on the data still filtered by the original filter context; hence, the maximum date will be taken each time from the month currently considered by Power BI in its iteration through the x-axis. Going back to our example of the month of the October, the MAX(Sales[SaleDate]) would return the 31st of October, but if Power BI were constructing the column of May of our graph, the MAX(Sales[SaleDate]) would have returned May 31st, and so on for each month. Our ALL function is necessary because we want to consider all of the SaleDates when comparing them to the current maximum date, and not just the SaleDates from the currently considered month. Without the ALL, we would not obtain a cumulative sum, but a simple total for each month in our bar chart. Again from our example of the month of October, Sales[SaleDate] <= MAX(Sales[SaleDate]) can be translated to Sales[SaleDate] <= 31/10/2022 (assuming 2022 as the year); it is just the first portion of this expression, the Sales[SaleDate] column, that is affected by the ALL: with the ALL, we consider every date before the 31st of October also coming from previous months, effectively obtaining a cumulative sum for the month of October. Without the ALL, the original filter context would stay unchanged and the first Sales[SaleDate] would only consider the dates from October, excluding any other month. The ALL is not applied to the MAX(Sales[SaleDate]), which changes dynamically every time Power BI is considering a specific month: this is how this measure actually manages to return a proper cumulative sum, different for each element of our x-axis.

After all these operations, once our measure has been evaluated, the CALCULATE will re-apply the original filter context, so that any other measure or visual will not be affected by this temporary change in the filter context.

I hope I managed to be clear enough: CALCULATE, FILTER and ALL can of course be used in a huge number of scenarios, not just for cumulative sums. Once you get the hang of them, you will realize just how much you can do. Based on my limited experience, here below are some personal suggestions that may help you along the way:

• Try to have a clear idea of all the filters that will affect your visual: keep in mind that filters will be propagated via the relationships that you have setup between your tables. Your model view in Power BI can give you a better idea of the expected filter flow.
• You can use multiple FILTER functions inside the same CALCULATE: their filters will be combined with a logical AND. This scenario will come in handy whenever you need to consider the filters of multiple tables.
• Remember that you can use the ALL function also while referencing single columns, not necessarily an entire table: if you need to keep all the filters from the original filter context, but you need for a particular measure to remove just one or a few filters for specific columns, you can use the FILTER function with ALL(Table[Column1], Table[Column2], etc.)as the first parameter. The original table, then, will have more rows, depending on the removed filters: any other existing filter, though, will still be active and will be combined with the additional filters that you specify with a logical AND.
• For a more comprehensive guide on the DAX language and its inner functionalities, I would suggest to check out “The Definitive Guide to DAX”, by Marco Russo and Alberto Ferrari (Chapter 5 and Chapter 6 would be your go-to for the topics of this article).

Happy reporting!