Sum the value of the next 4 dates with the same brand value

[Anonymous]

Hi,

I have got a sample table like below where it is by week, by retailer and by brand, with its QTY.

I now need to create a New column from Query by M, so that it sums the QTY from the "next" 4 dates with the same Retailer and Brand. (So in the below example, the first value of this column is 10 + 6 + 3 +10 = 29, that is the sum of the next 4 dates of QTY from Retailer X and Brand A).

Date	Retailer	Brand	QTY	New column (Sum QTY of next 4 dates)
01/01/2023	X	A	10	29
01/01/2023	Y	A	5
01/02/2023	X	A	6
01/02/2023	Y	A	2
01/03/2023	X	A	3
01/03/2023	Y	B	5
01/04/2023	X	A	10
01/04/2023	Y	B	20
01/04/2023	Y	B	15

I have tried List.Sum and FirstN but cannot eventually get it right. Any help please!

[Anonymous]

Hi @Anonymous ,

Please check: Is this the output you expected?

Advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lGKAGJHIDY0UIrVQZGJhMqYQiWM0LWYoUvAdBhBJYzRdRijS4B0OCHZYYLTWSboWoxwyhgCTYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Retailer = _t, Brand = _t, QTY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Retailer", type text}, {"Brand", type text}, {"QTY", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(Table.SelectRows(#"Added Index",(x)=> x[Retailer] = [Retailer] and x[Brand] = [Brand] and x[Index] >= [Index])[QTY],4)))
in
    #"Added Custom"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Syndicate_Admin]

Can you indicate where the result differs?

[Syndicate_Admin · Accepted solution]

Your "Next 4 dates"  is ambiguous.  Did you mean "current row plus next three rows"?  Did you mean "current date and next three days?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR2lCCB2BGJDA6VYHTS5SKicKVzKCF2bGaYUTJcRXMoYXZcxphRIlxOKXSZ4nGiCrs0Ij5wh0MxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Retailer = _t, Brand = _t, QTY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Retailer", type text}, {"Brand", type text}, {"QTY", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sum Next Four", (k)=> List.Sum(Table.FirstN(Table.SelectRows(#"Changed Type", each [Retailer]=k[Retailer] and [Brand]=k[Brand] and [Date]>=k[Date]),4)[QTY]),Int64.Type)
in
    #"Added Custom"

[Anonymous]

Hi, thanks for your reply. It meant to be the next 4 "time period", for exmaple on the row of Day 1, it sums and shows the sum of QTY from D2 to D5, with its same brand and retailer.

I think you method is close to the final result I want to get, but it doesn't sum to the right QTY.