How to create Running Total in Power BI: The Basics

In this tutorial lets see how easy it is to create a running total in DAX either in SSAS, Power Pivot or Power BI.

Running total is a calculation that starts at a certain time period and then ends at the current period (read as current cell, either in Excel or a visual in Power BI).

In Excel creating a running total is pretty easy, you can use boolean logic inside SUMPRODUCT.

To be able to create same logic in Power BI requires some understanding of Filter Context and Row context and now lets see how to create running total in Power BI.

I am going to not follow the best practice of using a date table in the begining as that will confuse a person just starting with Power BI and DAX.

Here is the same setup that we have already seen in Excel:

First we need to create a new measure and it will be named as Running Total, it will be a simple measure containing MAX ( Data[Date] ) where Data being the name of the table and Date being the column of the Data table

As you can see at each cell MAX returns the MAX date visible in the filter context, a filter context is created by the rows, columns, slicers, filter pane in a report, there are others ways as well such creating filter context manually using CALCULATE & CALCULATETABLE.

At each cell of the report the filter context is the the cell itself, on 6th January MAX is evaluated in a filter context which is filtering Data[Date] = "1/6/2021" hence MAX only sees one date that's why we get 1/6/2021, but at the grand total the story is different, at the grand total there is not filter context, so MAX is evaluated in a filter context containing all the dates of the model, that's why MAX returns 1/10/2021 at the grand total.

In Excel I used $B$3:$B$12<=B12, where I simply tried to filter the dates that are less than the dates of the current cell (B12), the same logic needs to applied in DAX as well, but its not as easy as that because in DAX there is no concept of A1 or B2 DAX only knows tables and columns and it can iterate them with the help of iterating/looping functions.

First we need to obtain all the dates that are in the data model, and to that we can use the function ALL. And even before that I need to store MAX in a variable so that we can "Debug" our code and make it more readable.

As shown in the below image ALL gets all the dates in the table and to prove that we are getting all the dates I used COUNTROWS to count the number of dates stored in the variable ALLDates.

Now what we can do is for each cell of the Matrix we can loop and check for the dates in ALLDates variable that are less than or equal to the variable MaxVisibleDate and to do that we can use FILTER function.

FILTER is a table function in DAX which simply iterates row by row and checks for a boolean condition provided in the second argument, once the condition is satisfied it returns a table that is either smaller or equal to the size of the original table.

Notice how for each cell the number is incrementing? We are going to use that for calculating the running total, 2 on the second row means there are 2 dates that are less than or equal to 1/2/2021, which are 1/1/2021 and 1/2/2021, the same deduction can be made for other cells as well.

Now we are going to apply these dates that are being returned by the FILTER function into the filter context using CALCULATE function.

Here is some commentary on how CALCULATE is working.

Now once you understand how the above calculation works, it is time to use a Date table as whenever you perform time intelligence calculations it is always a best practice to use a centralized date table.

I would suggest you always create a date table in Power Query, but if you are in a hurry you can use the DAX Date table mentioned here -

To create a date table in Power Query refer to this blog: https://www.antmanbi.com/post/create-a-date-table-in-power-query-with-m-code

I have imported the date table and built a relationship between Date table and the Data table.

Now I need to simply mark the Dates table as a Date table using the UI and then go back to the report and instead of using the Date column from Data table I need to use the Date column from the Dates table and modify the code to use Date column from the Dates table.

In the below image you will notice that the running total is continued for the dates that do not have the Total Sales. The reason is that when FilterDates variable is applied to the filter context it overwrites any existing filter context on the same column, now my Dates table has Dates for the whole year but the Data table only contains data from 1/1/2021 till 1/10/201 so it doesn't make any sense to show 472 for the dates after 1/10/2021.

The code of the above image:

Running Total =
VAR MaxVisibleDate =
    MAX ( Dates[Date] )
VAR ALLDates =
    ALL ( Dates[Date] )
VAR FilterDates =
    FILTER ( ALLDates, Dates[Date] <= MaxVisibleDate )
VAR Result =
    CALCULATE ( [Total Sales], FilterDates )
RETURN
    Result

To solve the issues what we can do is find the last date in Data Table when there was any sales.

Here is the update code with comments

Running Total =
VAR LastDateWithSales =
    -- Calculates MAX date in an empty filter context
    -- REMOFITLERS removes all the filters from the model
    CALCULATE (
        MAX ( Data[Date] ),
        REMOVEFILTERS ( Data )
    )
VAR MaxVisibleDate =
    MAX ( Dates[Date] )
VAR Result =
    IF (
        -- If date in the current cell is less than or equal to 
        -- the last date ever with sales then return running total
        MaxVisibleDate <= LastDateWithSales,
        VAR ALLDates =
            ALL ( Dates[Date] )
        VAR FilterDates =
            FILTER ( ALLDates, Dates[Date] <= MaxVisibleDate )
        RETURN
            CALCULATE ( [Total Sales], FilterDates ),
        -- At the grand total we will get a blank thats why I have used
        -- [Total Sales] to show grand total
        [Total Sales]
    )
RETURN
    Result

End result: