topic Calculating Current Value using Previous Row's Calculated Value in DAX Commands and Tips

Calculating Current Value using Previous Row's Calculated Value

Poffarbacco — Wed, 07 Aug 2024 12:10:18 GMT

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

Re: Calculating Current Value using Previous Row's Calculated Value

OwenAuger — Wed, 07 Aug 2024 13:36:18 GMT

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? 🙂

Re: Calculating Current Value using Previous Row's Calculated Value

Greg_Deckler — Wed, 07 Aug 2024 13:43:39 GMT

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

Re: Calculating Current Value using Previous Row's Calculated Value

Poffarbacco — Thu, 08 Aug 2024 14:24:24 GMT

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?

Re: Calculating Current Value using Previous Row's Calculated Value

ImkeF — Thu, 22 Aug 2024 20:26:34 GMT

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