Calculating Current Value using Previous Row's Calculated Value

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