Conditional Running total with set offs

[mayanknamasys]

Hi Community,

For last 7 days, I am trying to create a DAX Measure/Calculated Column for calculating a running total with conditional set offs. In short, the logic requires that if the manager has a deficit during the month, it will carried forward to next months but if the manager has surplus, the surplus will either be paid during the month or set off against the deficit of previous months.

The detailed logic with examples is explained in the column K of attached excel as well as the screenshot.

I have been able to create this logic in Excel file (Column H) quite easily because excel provides control over each row but I have had no success yet in DAX (I have attached the Excel file with solution as well as the power bi file with sample data). 

Thank you in Advance.

Excel file link:  data.xlsx
Power BI file link: data.pbix

[Anonymous · Accepted solution]

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.

[lbendlin]

Conditional aggregation is impossible in DAX.  Your only chance of implementing this is in Power Query via List.Accumulate()

[mayanknamasys]

Hi @lbendlin , I will try this List.Accumulate() today but I have not worked with it earlier. If you have worked with it, can you give me some insights on how it might work?

[lbendlin]

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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Desired Output", each List.Accumulate({0..[Index]},0,(s,c)=>List.Min({0,s+#"Added Index"{c}[#"Comp@30%"]-#"Added Index"{c}[#" Monthly Base "]})))
in
    #"Added Custom"

Now you need to break this up by Manager name.