DAX Query Issue: Calculating Current Value using Previous Row's Calculated Value

Question

I have a dataset representing battery state changes, and I need to calculate the ResidualCapacity for each state change. The ResidualCapacity should be calculated as the previous ResidualCapacity plus the current ChargeVariation, without exceeding an UpperBound of 80.

Here's the structure of my table with the correct **ResidualCapacity**value:

ChangeState	ChargeVariation	ResidualCapacity
1	70	70
2	-5	65
3	20	80
4	-10	70
5	-5	65

• ChangeState: Index showing the chronological order of battery state changes.
• ChargeVariation: The amount by which the charge has varied.
  • ChargeVariation < 0: Battery usage
  • ChargeVariation > 0: Battery charging
• ResidualCapacity: A calculated column that should show the residual battery capacity after each state change.

My goal is for ResidualCapacity to always be the previous ResidualCapacity plus the current ChargeVariation, but it should never exceed the UpperBound of 80.

I've written the following DAX query, but it returns an incorrect result for the last value of ResidualCapacity (it returns 70 instead of 65):

ResidualCapacity = 
    VAR UpperBound = 80
    VAR CurrentVariation = 'Table'[ChargeVariation]
    VAR CurrentState = 'Table'[ChangeState]

    VAR PervVariationRT = 
        SUMX(
            FILTER(
                'Table',
                'Table'[ChangeState] < CurrentState
            ),
            'Table'[ChargeVariation]
        )

    VAR Result =
        MIN(CurrentVariation + MIN(PervVariationRT, UpperBound), UpperBound)

    RETURN
        Result

How can I modify this query to correctly calculate the ResidualCapacity calculated column for each state change?

Answer 1

Disclaimer: I received a solution in the Fabric community. Here is the link to the solution.

For those interested, below is a comment on the various solutions tested between those received here on Stack Overflow and those from the Fabric community.

As I mentioned in the Fabric community in response to Greg_Deckler's solution, the real table is composed of 61K rows divided into different proportions over 16 different IDs and is structured as follows:

ID	Index	Value
A	1	...
A	...	...
A	N	...
---	---	---
P	1	...
P	...	...
P	N	...

I tested the various proposed codes and these are my comments:

• Solutions received on Stack Overflow:

  • @Sam_Nseir's DAX Solution: The first calculated column is generated in about 3 minutes, but the second is much more costly and I get the error "Not enough memory to complete this operation."
  • Comparison of Power Query solution between @Ron_Rosenfeld and @davidebacci: During execution, Ron's input table size grows much faster than Davide's, but both significantly increase the input table size. After about 10 minutes of calculation, Ron's proposed solution reached 30GB of memory while Davide's reached about 7GB.

• Solutions from the Fabric community:

  • Greg_Deckler: Proposed a DAX solution that generates the error "Not enough memory to complete this operation."
  • OwenAuger: Proposed the optimal solution. I tested the DAX code and after 3 minutes of query, I obtained the expected output.