Cumulative sum with conditons and reset sum

[TK12345]

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 Binnen then amount, as long as the next row has the status Binnen then the sum needs to be cumulative. If it is Buiten the cumulative sum needs to reset to 0 and start all over.  Besides that we have multipe customers. So for each customer we need a new cumulative sum. I think I need to use the sorting for Date and Customer. And need indexes, but that only works for 2 rows insteat of cumulative. 

What do I need to do to solve this?

Date	Document	Customer	Amount	Status	EXPECTED OUTCOME
1-1-2020	factuur	1	23,35	Binnen	23,35
2-1-2020	factuur	1	34,34	Binnen	57,69
3-1-2020	factuur	1	155,99	Binnen	213,68
4-1-2020	factuur	1	203,87	Binnen	417,55
5-1-2020	credit	1	270,19	Binnen	687,73
6-1-2020	factuur	1	56,89	Buiten	0
7-1-2020	factuur	1	10,53	Binnen	10,53
8-1-2020	factuur	1	125,14	Binnen	135,67
9-1-2020	factuur	1	129,46	Binnen	265,13
10-1-2020	factuur	1	133,77	Binnen	398,90
1-1-2020	factuur	2	138,09	Binnen	138,09
2-1-2020	factuur	2	142,40	Binnen	280,49
3-1-2020	factuur	2	146,72	Buiten	0
4-1-2020	factuur	2	151,04	Binnen	151,04
5-1-2020	credit	2	155,35	Buiten	0
6-1-2020	factuur	2	159,67	Binnen	159,67
7-1-2020	factuur	2	163,98	Binnen	323,65
8-1-2020	factuur	2	168,30	Binnen	491,95
9-1-2020	factuur	2	172,61	Binnen	664,56
10-1-2020	factuur	2	176,93	Binnen	841,49

[AlienSx · Accepted solution]

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

[Adamboer]

To achieve the expected outcome in Power BI, you can follow these steps:

1. Import the data into Power BI and load it into the Power Query Editor.
2. Sort the data by date and customer by right-clicking on each column header and selecting Sort Ascending.
3. Add a custom column by clicking on the Add Column tab and selecting Custom Column. Name the column "Cumulative Sum."
4. 1. Add another custom column named "Expected Outcome" and use the following formula:In the formula bar, enter the following formula:
      
      if [Status] = "Binnen" then List.Accumulate(
      Table.Range(#"Sorted Rows", 0, [Index]),
      0,
      (state, current) => state + current[Amount]
      )
      else 0
      
      
      

      This formula creates a cumulative sum for each row where the status is "Binnen" and resets to 0 for each row where the status is "Buiten." 5. Expand the custom column by clicking on the expand icon next to its header and selecting the "Cumulative Sum" column.

if [Status] = "Binnen" then [Cumulative Sum]
else 0

This formula sets the Expected Outcome column equal to the cumulative sum for each row where the status is "Binnen" and 0 for each row where the status is "Buiten." 7. Close and apply the Power Query Editor changes and create a table visualization in Power BI with the columns Date, Document, Customer, Amount, Status, and Expected Outcome.

This should result in a table with the expected outcome values calculated as desired, with the cumulative sum resetting to 0 for each row where the status is "Buiten" and calculated separately for each customer.