How create a dynamic cumulative sum in power query

Question

I want to create a sum in power query (Measure C below) returns the cumulative sum of Measure A + Measure B and is limited to zero and which resets at the beginning of each Period as shown in the sample data. I have tried 2 indexes and self joins but can't get the results I want...

Answer 1

List.Generate is your best friend here - just give it some list to iterate. Ron Rosenfeld's solution uses the same approach. But he decided to go with list of records (table rows) while I am using list of lists (also table rows).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    rows = List.Buffer(Table.ToList(Source, (x) => x)),
    gen = List.Generate(
        () => [i = 0, r = rows{0}, mc = List.Min({0, r{2} + r{3}})],
        (x) => x[i] < List.Count(rows),
        (x) => [
            i = x[i] + 1, 
            r = rows{i}, 
            mc = List.Min({0, r{2} + r{3} + (if r{1} <> x[r]{1} then 0 else x[mc])})],
        (x) => x[r] & {x[mc]}
    ),
    result = Table.FromList(gen, (x) => x, Table.ColumnNames(Source) & {"Measure_C"})
in
    result

Answer 2

You can Group by Period, and use List.Generate to generate the running balance:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event Date", type date}, {"Period", Int64.Type}, {"Measure A", Int64.Type}, {"Measure B", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Period"}, {
        {"Add Measure C", (t)=>
            Table.FromColumns(
                Table.ToColumns(t)
                & {List.Generate(
                    ()=>[c=List.Min({0, t[Measure A]{0} + t[Measure B]{0}} ), idx=0],
                    each [idx] < Table.RowCount(t),
                    each [c=List.Min({0, [c]+t[Measure A]{[idx]+1} + t[Measure B]{[idx]+1}}), idx=[idx]+1],
                    each [c])},
               {"Event Date", "Period", "Measure A", "Measure B", "Measure C"}),
               type table[Event Date=date, Period=Int64.Type, Measure A=Int64.Type, Measure B=Int64.Type, Measure C=Int64.Type]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Period"}),
    #"Expanded Add Measure C" = Table.ExpandTableColumn(#"Removed Columns", "Add Measure C", {"Event Date", "Period", "Measure A", "Measure B", "Measure C"})
        
in
    #"Expanded Add Measure C"