Adding two single cells together to create rolling total.

Hey jkesavan ,

unfortunately there is nothing such like a cell in Power BI, not to mention that there is no such concept like a sequence. This is due to the fact that data is stored in tables, and even if most of the time we are referencing columns in measures and calculated columns, we have to face the challenge of unsorted rows inside these tables.

For this reason, the solution for your requirement may look a little exaggerated, but be assured it's nevertheless blazingly fast, at least most of the time.

Here is a DAX statement that I'm using to calculate a measure that I'm calling "SalesAmount cumulated":

SalesAmount cumulated = 
CALCULATE(
    SUM('FactSales'[SalesAmount])
    , FILTER(
        ALL('DimDate'[Datekey])
        , 'DimDate'[Datekey] <= MAX('DimDate'[Datekey])
    )
) 

 This is exactly doing what you are wanting, see the next screenshot:

What is essential for a working solution is a column with values that can be sorted implicitly sorted, like dates, integer, etc. This is because there is no such thing as an index that can be used for sorting.

The measure above works like this: aggregate (using the aggregation function SUM) all values from column SalesAmount across all rows that are filtered. The rows are filtered, by this filter expression (the 2nd parameter of the CALCULATE function from the DAX expression above): filter all rows where the datekey is less or equal to the current datekey. The current datekey is determined by the expression on the right hand side of the fcondition MAX(...).

As a lot of these rolling, gliding, cumulation requirements are related to time, I recommend reading this article about time related calculations: https://www.daxpatterns.com/time-patterns/

Hopefully, this provides what you are looking for.

Regards,

Tom