Forum Discussion

TK12345's avatar
TK12345
Resolver II
3 years ago
Solved

Cumulative sum with conditons and reset sum

Hi all,  I have a question I need to visualise in Power BI and want to do this by using the Power Query. I need to have the column Expected Outcome. This one is based on if the status equals Binne...
Need Help
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, TK12345 

    let
    text_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdI5CsMwEAXQu6j+gVm0trmGSRGygBsXwb5/jJSQSEjgQkjFYxD/zzQZPvFJSMjAPK+3ddte+4v3Iwp1+32el+WxmAsmIyOrFmprqyPLziGlGtvhJ0gRQ43dD99ej/u8fm0gcDPYjwY7j5jtNq8fG4Y/Jjit58ahFQduokhjnGB9jZmGWhWhyaLbnmQcQU0Y3foytgJLB/or2CNIHV23v4wdg5o4ev3JZzHKxv0Gd/srOME3YXQLzNgrUjzQYMER2oTRbTDjIPB8pMGiPdL/Jl3e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Document = _t, Customer = _t, Amount = _t, Status = _t]),
    amount_type = Table.TransformColumnTypes(text_table, {{"Amount", Currency.Type}}, "de-DE"),
    Source = Table.TransformColumns(amount_type, {"Date", each Date.FromText(_, [Format="M-d-yyyy", Culture="de-DE"])}),
    fx_outcome = (tbl as table) =>
        let 
            amounts = List.Buffer(tbl[Amount]),
            sts = List.Buffer(tbl[Status]),
            positions = List.Buffer(List.Skip(List.Positions(sts))),
            column_names = Table.ColumnNames(tbl) & {"outcome"},
            outcome =
                List.Accumulate(
                    positions,
                    {if sts{0} = "Buiten" then 0 else amounts{0}},
                    (s, c) => s & {if sts{c} = "Buiten" then 0 else amounts{c} + List.Last(s)}
                ),
            final = Table.FromColumns(Table.ToColumns(tbl) & {outcome}, column_names)
        in final,
    groups = Table.Group(Source, {"Customer"}, {{"all", each fx_outcome(Table.Sort(_, {"Date"}))}}),
    out = Table.Combine(groups[all])
in
    out
AlienSx's avatar
AlienSx
Super User
3 years ago

Hello, TK12345 

let
    text_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdI5CsMwEAXQu6j+gVm0trmGSRGygBsXwb5/jJSQSEjgQkjFYxD/zzQZPvFJSMjAPK+3ddte+4v3Iwp1+32el+WxmAsmIyOrFmprqyPLziGlGtvhJ0gRQ43dD99ej/u8fm0gcDPYjwY7j5jtNq8fG4Y/Jjit58ahFQduokhjnGB9jZmGWhWhyaLbnmQcQU0Y3foytgJLB/or2CNIHV23v4wdg5o4ev3JZzHKxv0Gd/srOME3YXQLzNgrUjzQYMER2oTRbTDjIPB8pMGiPdL/Jl3e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Document = _t, Customer = _t, Amount = _t, Status = _t]),
    amount_type = Table.TransformColumnTypes(text_table, {{"Amount", Currency.Type}}, "de-DE"),
    Source = Table.TransformColumns(amount_type, {"Date", each Date.FromText(_, [Format="M-d-yyyy", Culture="de-DE"])}),
    fx_outcome = (tbl as table) =>
        let 
            amounts = List.Buffer(tbl[Amount]),
            sts = List.Buffer(tbl[Status]),
            positions = List.Buffer(List.Skip(List.Positions(sts))),
            column_names = Table.ColumnNames(tbl) & {"outcome"},
            outcome =
                List.Accumulate(
                    positions,
                    {if sts{0} = "Buiten" then 0 else amounts{0}},
                    (s, c) => s & {if sts{c} = "Buiten" then 0 else amounts{c} + List.Last(s)}
                ),
            final = Table.FromColumns(Table.ToColumns(tbl) & {outcome}, column_names)
        in final,
    groups = Table.Group(Source, {"Customer"}, {{"all", each fx_outcome(Table.Sort(_, {"Date"}))}}),
    out = Table.Combine(groups[all])
in
    out