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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Quartarolo
Regular Visitor

Last 12 month sum for 2 categories in power query

Hello everyone!

 

I have a dataset similar to this:

 

Date              Dealership   

Car_Model   

Units_Sold
01/01/2022AX10
01/01/2022AY15
01/01/2022BX5
01/01/2022BY20
01/01/2022CX12
01/03/2022AX15
01/03/2022BY2
01/05/2022CX6
01/08/2022AY11
01/12/2022BX19
01/12/2022CY10
01/02/2023AX5

 

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

 

Date              Dealership     Car_Model    Units_Sold    Total_Sold_Last_12_Months
01/01/2022AX1010
01/01/2022AY1515
01/01/2022BX55
01/01/2022BY2020
01/01/2022CX1212
01/03/2022AX1525 (10+15)
01/03/2022BY222 (20 + 2)
01/05/2022CX618 (12 + 6)
01/08/2022AX1136 (10 + 15 + 11)
01/12/2022BX1924 (5 + 19)
01/12/2022CY1010
01/02/2023AX531 (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!

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors