Conditional Running total with set offs

Thanks for the helpful replies from lbendlin.

Hi mayanknamasys ,

If you want to sum for different manager names, you can first group by manager names, then add an index column within each group, and apply the List.Accumulate function to calculate the desired output. This ensures that the calculation for each Manager Name is done independently.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+/CoMwEMfxV5HMEe40Rh1dHASfIDhcaUEQQhE6+Pat5M+hTbYbPvy4rzFipoPsJqTAciJbVs3vLBAkABTn2btrkRc6vh5MG08Rk3amPViMs1gl7fAOto2zbWb18FJL7aVmOdjPTs+VbmFdqutiuayPtnO//Nl0GWSWuQ1r1nV2O/ShYq28Xr4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Manager Name" = _t, #"Month Name" = _t, #" Monthly Base " = _t, #"Comp@30%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Name", type date}, {" Monthly Base ", Currency.Type}, {"Comp@30%", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Manager Name"}, {{"AllData", each _, type table [#"Manager Name"=nullable text, #"Month Name"=nullable date, #" Monthly Base "=nullable number, #"Comp@30%"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Desired Output", each 
        let
            GroupedTable = [AllData],
            IndexedTable = Table.AddIndexColumn(GroupedTable, "Index", 0, 1, Int64.Type),
            CustomColumn = Table.AddColumn(IndexedTable, "Desired Output", each List.Accumulate({0..[Index]},0,(s,c)=>List.Min({0,s+IndexedTable{c}[#"Comp@30%"]-IndexedTable{c}[#" Monthly Base "]})))
        in
            CustomColumn),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Desired Output", {"Month Name", " Monthly Base ", "Comp@30%", "Index", "Desired Output"})
in
    #"Expanded Custom"

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.