Calculating Current Value using Previous Row's Calculated Value

[Poffarbacco]

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

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

How can I modify this query to correctly calculate the ResidualCapacity calculated column for each state change?
It is also OK if the solution is written in M Power Query

[ImkeF]

Hi @Poffarbacco ,
yes, Power Query would be more efficient.
Please check out my solution that @OwenAuger  mentioned above. 
It did the trick for me.

[Greg_Deckler]

@Poffarbacco Nifty little pseudo recursion problem. Here is my solution. Attached PBIX contains both column and measure forms.

Column = 
    VAR __UpperBound = 80
    VAR __CurrentState = [ChangeState]
    VAR __StartState = MINX( ALL('Table'), [ChangeState])
    VAR __StartValue = MINX( FILTER( ALL('Table'), [ChangeState] = __StartState ), [ChargeVariation] )
    VAR __Table = FILTER( ALL('Table'), [ChangeState] <= __CurrentState )
    VAR __Table1 = ADDCOLUMNS( __Table, "__Value", SUMX( FILTER( __Table, [ChangeState] <= EARLIER( 'Table'[ChangeState]) ), [ChargeVariation] ), "__Upper", __UpperBound )
    VAR __Table2 = ADDCOLUMNS( __Table1, "__Subtract", IF( [__Value] > [__Upper], [__Value] - [__Upper], 0 ) )
    VAR __Result = SUMX( __Table2, [ChargeVariation]) - SUMX(__Table2, [__Subtract] )
RETURN
    __Result

[Poffarbacco]

In the actual problem, the calculated column cannot be calculated due to excessive memory usage by the query "Not enough memory to complete this operation". The memory usage, which can be seen from Task Manager, is shown below:

Here is the link to download .pbix and .csv file of the fact table with real dimensions. The actual table is slightly different from the one presented in the toy case. It consists of 61k rows divided into 16 different IDs (vehicle identifiers), with each ID having between 2k to 6k rows.

More specifically, the table is structured as follows:

• ID: Vehicle Identifier
• Index: Sequential Number
• Value: Load absorbed or accumulated

I have rewritten the query by adapting it to the specific case and trying to optimize it. The result is as follows:

Column =
VAR __UpperBound = 80
VAR __CurrentIndex = [Index]
VAR __CurrentID = [ID]

VAR __StartIndex = 1
VAR __StartValue =
    CALCULATE (
        MIN ( [Value] ),
        'Table'[Index] = __StartIndex,
        'Table'[ID] = __CurrentID
    )
	
VAR __Table =
    FILTER ( ALL ( 'Table' ), [Index] <= __CurrentIndex  && [ID] = __CurrentID)
VAR __Table1 =
    ADDCOLUMNS (
        __Table,
        "__Value",
		VAR __InnerIndex = 'Table'[Index]
		VAR __InnerID = 'Table'[ID]
		RETURN
            SUMX (
                FILTER ( __Table, [Index] <= __InnerIndex && [ID] = __InnerID ),
                [Value]
            ),
        "__Upper", __UpperBound
    )
VAR __Table2 =
    ADDCOLUMNS (
        __Table1,
        "__Subtract",
            IF ( [__Value] > [__Upper], [__Value] - [__Upper], 0 )
    )
VAR __Result =
    SUMX ( __Table2, [Value] - [__Subtract] )
RETURN
    __Result

Any help in optimising this DAX query and reducing memory consumption would be greatly appreciated. Would a PowerQuery solution be more efficient? 

[OwenAuger · Accepted solution]

Hi @Poffarbacco 

Here are some options (PBIX link here since forum attachment wasn't working):

1. DAX calculated column:

ResidualCapacity DAX = 
VAR UpperBound = 80
VAR CurrentState = 'Table'[ChangeState]
VAR StateHistory =
    FILTER ( ALL ( 'Table'[ChangeState] ), 'Table'[ChangeState] <= CurrentState )
-- Cumulative ChargeVariation for all States up to and including current
VAR CumulativeTable =
    ADDCOLUMNS (
        StateHistory,
        "@Cumulative",
        VAR CurrentStateInner = 'Table'[ChangeState]
        RETURN
            CALCULATE (
                SUM ( 'Table'[ChargeVariation] ),
                'Table'[ChangeState] <= CurrentStateInner,
                REMOVEFILTERS ( 'Table' )
            )
    )
VAR MaxCumulative =
    MAXX ( CumulativeTable, [@Cumulative] )
VAR CurrentCumulative =
    SELECTCOLUMNS (
        FILTER ( CumulativeTable, 'Table'[ChangeState] = CurrentState ),
        [@Cumulative]
    )
-- Adjustment required for largest exceedance of Threshold so far
VAR Adjustment =
    MIN ( UpperBound - MaxCumulative, 0 )
VAR Result =
    CurrentCumulative + Adjustment
RETURN
    Result

2. Power Query

Adapt Imke Feldmann's Power Query function for adding a cumulative column from this post. (Shout out to @ImkeF! )

Here is the modified version of the function that allows for nullable UpperBound and LowerBound parameters:

let
  func = (
    Table as table,
    RunningTotalName as text,
    SortColumn as text,
    AmountColumn as text,
    UpperBound as nullable number,
    LowerBound as nullable number
  ) =>
    let

      // Sort table and buffer it
      Sorted = Table.Buffer(
        Table.AddIndexColumn(Table.Sort(Table, {{SortColumn, Order.Ascending}}), "Index", 1, 1)
      ),
      // Select the Columns
      SelectColumns = Table.SelectColumns(Sorted, {SortColumn, AmountColumn, "Index"}),
      // Extract Amount column and buffer it
      ExtractAmountColumn = List.Buffer(Table.Column(SelectColumns, AmountColumn)),
      // Calculate a list with all running Totals
      RunningTotal = List.Skip(
        List.Generate(
          () => [ListItem = 0, Counter = 0],
          each [Counter] <= List.Count(ExtractAmountColumn),
          each [
            ListItem =
              let
                Value = ExtractAmountColumn{[Counter]} + [ListItem]
              in
                List.Max({List.Min({Value, UpperBound}), LowerBound}),
            Counter = [Counter] + 1
          ]
        ),
        1
      ),
      ConvertedTable = Table.FromList(
        RunningTotal,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
      ),
      ExpandedColumn = Table.ExpandRecordColumn(
        ConvertedTable,
        "Column1",
        {"ListItem", "Counter"},
        {"ListItem", "Counter"}
      ),
      MergedQueries = Table.NestedJoin(
        Sorted,
        {"Index"},
        ExpandedColumn,
        {"Counter"},
        "Expanded Column1",
        JoinKind.LeftOuter
      ),
      Expand = Table.ExpandTableColumn(
        MergedQueries,
        "Expanded Column1",
        {"ListItem"},
        {RunningTotalName}
      ),
      #"Removed Columns" = Table.RemoveColumns(Expand, {"Index"}),
      #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Columns",
        {{RunningTotalName, type number}}
      )
    in
      #"Changed Type"
  ,
  documentation = [
    Documentation.Name            = " Table.ColumnRunningTotal",
    Documentation.Description     = " Fast way to add running total to a table",
    Documentation.LongDescription = " Fast way to add running total to a table",
    Documentation.Category        = " Table",
    Documentation.Source          = " local",
    Documentation.Author          = " Imke Feldmann: www.TheBIccountant.com",
    Documentation.Examples        = {[Description = " ", Code = " ", Result = " "]}
  ]
in
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))                                                                         

Applied to the table in the attached PBIX it looks like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI3gBCxOtFKRkCmrimQMDMF842BTCOQvAVE3gQkb4ikwRRZQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ChangeState = _t, ChargeVariation = _t, #"ResidualCapacity Expected" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ChangeState", Int64.Type}, {"ChargeVariation", Int64.Type}, {"ResidualCapacity Expected", Int64.Type}}),
    #"Add Running Total" = ColumnRunningTotal(#"Changed Type", "ResidualCapacity PQ", "ChangeState", "ChargeVariation", 80, null)
in
    #"Add Running Total"

Were these the sort of thing you were looking for? 🙂