Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 12 Months Column in Power Query

Let's say I have this fake data:   Reference Date Component Monthly Removals 09/01/2021 A 1 12/01/2021 A 2 03/01/2022 A 5 05/01/2022 A 0 04/01/2022 B 3 08/01/2022 ...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc9LDsAgCATQu7A2cUBt7LK9hvH+1xA/qZbFhMVLBiiFcHuwFwiTo0fDI9UVYjEkmjAIYZEsSpo8KRnCpnjQO8q+wmyo74zzDBjCJjbU5/U/IxyF+ldt", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference Date" = _t, Component = _t, #"Monthly Removals" = _t, #"Removals L12M" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference Date", type date}, {"Component", type text}, {"Monthly Removals", Int64.Type}, {"Removals L12M", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
thisdate = [Reference Date],
thiscomponent = [Component],
prev12mrows = Table.SelectRows(#"Changed Type", each ([Component] = thiscomponent and ([Reference Date] <= thisdate and [Reference Date] >= Date.AddMonths(thisdate, -12)))),
sum = List.Sum(prev12mrows[Monthly Removals])
in 
sum)
in
    #"Added Custom"

    Pat

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi,

 

 Not sure I fully follow the logic as your first line 09/01/21 isn't within the last 12 months but you have summed it?

Also does it need to have the sum at each line as per example or would a total applied to everyline suffice? i.e.

Reference DateComponentMonthly RemovalsRemovals L12M
09/01/2021A18
12/01/2021A28
03/01/2022A58
05/01/2022A08

 

if the latter is applicable you could add a helper column to group by Component and last 12 months, something like....

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference Date", type date}, {"Component", type text}, {"Monthly Removals", Int64.Type}}),
#"Added DateCheck" = Table.AddColumn(#"Changed Type", "DateCheck", each if Date.IsInPreviousNMonths([#"Reference Date"], 12) then "Yes" else "No"),
#"Grouped Rows" = Table.Group(#"Added DateCheck", {"Component", "DateCheck"}, {{"Group", each List.Sum([Monthly Removals]), type nullable number}}),
#"Added Removals Sum" = Table.AddColumn(#"Grouped Rows", "Monthly Removals Sum", each if [DateCheck] = "No" then null else [Group], type number)
in
#"Added Removals Sum"

 

 

then merge back into orginal query.

 

Thanks,

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi James,

 

I need to sum the L12M removals relative to the each reference date. For example, on the first line, L12M would be 10/01/2020 to 09/01/2021.