Recursive Carried/Brought Forward

Hi devans91, do you need all calculated columns or is it enough to calc just Carried Forward cols?

Output:

You have to specify columns (as a list) for which you want to calculate Carried Forward col:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJV0lEKKMovSC0qqQQyA0vzS1KBtKEBkADJmQCxsVKsTrSSEV4NFkBshqLeGK96c7A6ZPUm+B1kBFWM0GCKV4Ml1BKEejP8Fhhi+MCc1CBCClPH0pJ8kL78nMzkSpj7zWHaLNBDFF25JdRcC7AdaMGJYbYpzFywLZZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Line of Business" = _t, #"Doc Type" = _t, #"Input Required" = _t, #"QC Required" = _t, #"Input Allocated" = _t, #"QC Allocated" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Input Required", Int64.Type}, {"QC Required", Int64.Type}, {"Input Allocated", Int64.Type}, {"QC Allocated", Int64.Type}}),
    ColsToCalculate = {"Input", "QC"},
    F = (tbl as table, cols as list)=>
                List.Transform(cols, (r)=> 
                    [ a1 = List.Buffer(Table.ToRows(Table.SelectColumns(tbl, List.Transform({"Required", "Allocated"}, (x)=> Text.Combine({r, x}, " "))))),
                      a2 = List.Generate(
                                ()=> [ x = 0, y = a1{x}, z = List.Max({y{0} - y{1}, 0}) ],
                                each [x] < List.Count(a1),
                                each [ x = [x]+1, y = a1{x}, z = List.Max({y{0} - y{1} + [z], 0}) ] ,
                                each [z] )
                    ][a2] ),
    StepBack = ChangedType,
    Ad_CarriedForwardCols = Table.Combine(Table.Group(StepBack, {"Line of Business"}, {{"All", each _}, {"T", each
        [ cols = List.Transform(ColsToCalculate, (x)=> x & " Carried Forward"),
          a = Table.FromColumns(Table.ToColumns(_) & F(_, ColsToCalculate), Table.ColumnNames(_) & cols),
          b = Table.TransformColumnTypes(a, List.Transform(cols, (x)=> {x, type number}))
        ][b], type table}})[T])
in
    Ad_CarriedForwardCols