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

jgeddes's avatar
jgeddes
Super User
1 year ago

jnfl This should have been created as a new topic with a reference to any previous similar solutions.
That being said, you can add a column to your existing query with the following code...

= Table.AddColumn(#"Changed Type", "last12dap", each if [mo] > 12 then List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[id]= [id] and x[date] >= Date.AddMonths([date], -12))[dap]) else null, type number)

Any references to #"Changed Type" should be replaced with the previous step in your query.

jnfl's avatar
jnfl
New Member
1 year ago

Oh, wow--this is terrific.  Thank you!  I haven't completed testing it (my dataset is very large), but the first time I ran it, I realized that there were numbers greater than 12 (which shouldn't happen if it's only summing the 12 months prior to that date).  So, I added another part to the Table.SelectRows function so that it now reads,

 

= Table.AddColumn(#"Changed Type", "last12ddelq", each if [month] > 12 then List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[loan_id]= [loan_id] and x[date] >= Date.AddMonths([date], -12) and x[date]< [date])[delinquency_b]) else null, type number)

 

I'll post again if/when it completes and hopefully works.  Thanks, jgeddes!  (And duly noted on the new thread--thanks.)