Recursive Carried/Brought Forward

[devans91]

Hello,

I'm working on a dataset which needs to find the difference between a Resource Required and Resource Allocated for a particular day and carry it forward to the next relevant row. For example if on 1/1/25 Resource Required = 3, Resource Allocated = 2.5 then the Carried Forward = 0.5. I also somehow need to make sure that this value is at least 0, so any negative value is replaced with 0.

I want this 0.5 figure to be carried over to the next row and then added to the Resource Required, eg. 2/1/25 Resource Required = 2, Resource Allocated = 2.5, Brought Forward = 0.5 then I'd like to create a Rolling Resource Required which should equal the Resource Required + Brought Forward = 2.5.

I tried doing a self-join by creating 2 Index columns (one shifted) but that only worked out the difference between 2 days and didn't carry forward over multiple days. I think it requires some sort of iteration but I just can't work it out!

The table below is what I'd like as a result. Essentially each check needs to match the Line of Business and Document Type and bring down the Carried Forward figures into the Brought Forward columns. 

Appreciate any help and hopefully this isn't as complicated as I think it is...!

Date	Line of Business	Doc Type	Input Required	QC Required	Input Brought Forward	QC Brought Forward	Rolling Input Required	Rolling QC Required	Input Allocated	QC Allocated	Input Carried Forward	QC Carried Forward
1/1/2025	Property	Quote	10	5	0	0	10	5	4	3	6	2
2/1/2025	Property	Quote	8	6	6	2	14	8	4	3	10	5
3/1/2025	Property	Quote	7	3	10	5	17	8	4	3	13	5
4/1/2025	Property	Quote	12	4	13	5	25	9	4	3	21	6
5/1/2025	Property	Quote	9	7	21	6	30	13	4	3	26	10
6/1/2025	Property	Quote	11	6	26	10	37	16	4	3	33	13
7/1/2025	Property	Quote	10	5	33	13	43	18	4	3	39	15
1/1/2025	Auto	Policy	12	7	0	0	12	7	10	8	2	0
2/1/2025	Auto	Policy	9	4	2	0	11	4	8	5	3	0
3/1/2025	Auto	Policy	15	10	3	0	18	10	12	9	6	1

[dufoq3 · Accepted solution]

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

[devans91]

Thank you, I was able to derive the rest of the required columns from the Carried Forward calculations. Honestly the solution felt like magic and can't thank you enough!

[dufoq3]

You're welcome. Enjoy 😉

[devans91]

Hi BBF, thanks for looking into this!

The input data is the Date, Line of Business, Doc Type, Input Required, QC Required, Input Allocated and QC Allocated. The rest of the columns I'd like to calculate.

[BeaBF]

@devans91 Hi! which are the input data?

BBF