Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
KennethMau
Regular Visitor

Cumulative sum by category and sorted ascending by date

Hi all!

 

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

 

IDWeek_Start_DateDemandReceiptsProjected StockStock Projected
A12/4/235050500500
A12/11/23200500350
B12/4/235520422422
B12/11/23100200342
B18/11/235000292

 

 

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

2 REPLIES 2
spinfuzer
Super User
Super User

 

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
Super User
Super User

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

wdx223_Daniel_0-1702452141011.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors