Last 12 month sum for 2 categories in power query

[Quartarolo]

Hello everyone!

I have a dataset similar to this:

Date	Dealership	Car_Model	Units_Sold
01/01/2022	A	X	10
01/01/2022	A	Y	15
01/01/2022	B	X	5
01/01/2022	B	Y	20
01/01/2022	C	X	12
01/03/2022	A	X	15
01/03/2022	B	Y	2
01/05/2022	C	X	6
01/08/2022	A	Y	11
01/12/2022	B	X	19
01/12/2022	C	Y	10
01/02/2023	A	X	5

And the result i'm looking for is shown below:

Date	Dealership	Car_Model	Units_Sold	Total_Sold_Last_12_Months
01/01/2022	A	X	10	10
01/01/2022	A	Y	15	15
01/01/2022	B	X	5	5
01/01/2022	B	Y	20	20
01/01/2022	C	X	12	12
01/03/2022	A	X	15	25 (10+15)
01/03/2022	B	Y	2	22 (20 + 2)
01/05/2022	C	X	6	18 (12 + 6)
01/08/2022	A	X	11	36 (10 + 15 + 11)
01/12/2022	B	X	19	24 (5 + 19)
01/12/2022	C	Y	10	10
01/02/2023	A	X	5	31 (15 + 11 + 5)

In parentheses are the numbers added to reach the final result, only to exemplify.

Disclaimer: the dates are in DD/MM/YYYY format.

The dates are always reported as start of month, an there can be months with 0 sales leading to gaps in the lines, since the sales are not reported.

To summarize, I need the last column to sum the units solds, per unit and dealership, in the last 12 months, acording to the Date in column 1. For example: in the last line, it needs to sum all units sold from model X, dealership A, in the period 01/03/2022 to 01/02/2023.

I also need this in power query because I need to merge it with another dataset.

Is there some way to do it? I've been cracking at this one for a while now. Thanks!

[wdx223_Daniel]

= Table.FromRecords(List.Accumulate(Table.ToRecords(Source),{{},[]},(x,y)=>let a=Record.TransformFields(x{1},{y[Dealership]&"-"&y[Car_Model],each List.Skip((_??{})&{{y[Date],y[Units_Sold]}},each _{0}<Date.AddMonths(y[Date],-12))},2) in {x{0}&{y&[Total_Sold_Last_12_Months=List.Sum(List.Zip(Record.Field(a,y[Dealership]&"-"&y[Car_Model])){1})]},a}){0})

[slorin · Accepted solution]

Hi

A solution with Join, Filter and Group

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMjJR0lRyCOAGJDA6VYHSxykSA5U0w5J6g+HFIgbUZYjHSGWWcElzPGcIopphzcTLiUKbqRZnApCwwPGMLkDI3QPWBoiSnnDNOH8AFYzhjJlUBHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Dealership = _t, Car_Model = _t, Units_Sold = _t]),
Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Units_Sold", type number}}),
Index = Table.AddIndexColumn(Type, "Index", 1, 1, Int64.Type),
AutoJoin = Table.NestedJoin(Index, {"Dealership", "Car_Model"}, Index, {"Dealership", "Car_Model"}, "Type", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(AutoJoin, "Type", {"Date", "Units_Sold"}, {"Date.1", "Units_Sold.1"}),
Filter_12_Previous_Months = Table.SelectRows(Expand, each [Date.1] > Date.AddMonths([Date],-12) and [Date.1] <= [Date]),
Group = Table.Group(Filter_12_Previous_Months, {"Date", "Dealership", "Car_Model", "Units_Sold", "Index"}, {{"Total_Sold_Last_12_Months", each List.Sum([Units_Sold.1]), type nullable number}}),
Sort = Table.Sort(Group,{{"Index", Order.Ascending}})
in
Sort

Stéphane