Cumulative sum by category and sorted ascending by date

[KennethMau]

Hi all!

Im currently working with a table in power query that looks like this:

ID	Week_Start_Date	Demand	Receipts	Projected Stock	Stock Projected
A	12/4/23	50	50	500	500
A	12/11/23	200	50	0	350
B	12/4/23	55	20	422	422
B	12/11/23	100	20	0	342
B	18/11/23	50	0	0	292

I need a new column "Stock Projection". Using the oldest (min for each id) date in the column Week Start Date as base to start the sum (in this case 12/4/23) perform a cumulative calculation of projected stock column (what i really need to do is to add the recipts and substrate the demand for that week , which are on other columns of the same table). So its like using the last week stock, adding the receipts, substrating the demand for each ID. in case the calculation results in a Stock less than 0, then return 0, so the next week the intial stock would be 0 instead of a negative value (because having negative stock its imposible)

is there a way to do this that doest take too long, im working with a dataset of around 20 000 rows

Any help is really apreciated

[spinfuzer]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00jfRNzIGskwNEISBUqwOXN7QEKLAyACuAiLvhKrfFKwGSJgYGSHLw/QbgvUboem3gMtDTUaWNTJAksUibYjkOCBhDJWOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Week_Start_Date = _t, Demand = _t, Receipts = _t, #"Projected Stock" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Week_Start_Date", type date}, {"Demand", Int64.Type}, {"Receipts", Int64.Type}, {"Projected Stock", Int64.Type}}),
    cumulative_sum = (tbl as table) =>
        let
             cumulative_sum_list= 
                List.Accumulate(
                    Table.ToRecords(tbl),
                    {},
                    (acc,curr) => 
                        acc 
                        & 
                        { 
                            List.Max(
                                {
                                    0,
                                    (List.Last(acc) ?? 0) + curr[Projected Stock] + Byte.From(curr[Projected Stock] = 0)*(curr[Receipts] - curr[Demand])
                                }
                            )  
                        }
                )
        in
            Table.FromColumns(Table.ToColumns(tbl) & {cumulative_sum_list}, Table.ColumnNames(tbl)&{"Stock Projected"}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"grouped", each cumulative_sum(_), type table [ID = nullable Text.Type, Week_Start_Date = nullable Date.Type, Demand = Number.Type, Receipts = Number.Type, Projected Stock = Number.Type, Stock Projected = Number.Type]}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"grouped"})
in
    #"Removed Other Columns"

[wdx223_Daniel]

suppose the date is sorted by ID and Week_Start_Date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLWNTTSNQGyTQ0QhIFSrA6qCkNDMAeuBqLCCd0MU7AAkDAxMsJQATbDEGyGEXYzDC0Q5oPlYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Week_Start_Date = _t, Demand = _t, Receipts = _t, #"Projected Stock" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Week_Start_Date", type date}, {"Demand", type number}, {"Receipts", type number}, {"Projected Stock", type number}}),
    Custom1 = Table.FromRecords(List.Generate(()=>{1,ChangedType[Projected Stock]{0}},each _{0}<=Table.RowCount(ChangedType),each {_{0}+1,if ChangedType[Projected Stock]{_{0}}>0 then ChangedType[Projected Stock]{_{0}} else List.Max({0,_{1}+ChangedType[Receipts]{_{0}}-ChangedType[Demand]{_{0}}})},each ChangedType{_{0}-1}&[Stock Projected=_{1}]))
in
    Custom1