Last 12 Months Column in Power Query

[Anonymous]

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	B	1
10/01/2022	B	0
11/01/2022	B	2
05/01/2023	B	1

I need to create a column that will calculate the Last 12 Month removals for each reference date and component, for example:

Reference Date	Component	Monthly Removals	Removals L12M
09/01/2021	A	1	1
12/01/2021	A	2	3
03/01/2022	A	5	8
05/01/2022	A	0	8
04/01/2022	B	3	3
08/01/2022	B	1	4
10/01/2022	B	0	4
11/01/2022	B	2	6
05/01/2023	B	1	1

I need to perform this in Power Query, because I need to merge it with another dataset.

I've been trying to "filter" (Table.SelectRows) the table based on the date and component, but I keep getting the cyclic reference error. Any idea on how I could approach this problem?

[jnfl]

I have a similar need, but can't seem to implement @ppm1 's code.  In power query, I need to create a new column (l12modap) that, for all ids with at least 12 months of dap data, will sum the last 12 months of said data. 

I have the first 4 columns and would like to see the 5th, ei.e., column E:

	A	B	C	D	E
1	id	date	mo	dap	l12modap
2	1001	6/1/2022	1	0		<= 12 = (blank)
3	1001	7/1/2022	2	0		<= 12 = (blank)
4	1001	8/1/2022	3	1		<= 12 = (blank)
5	1001	9/1/2022	4	0		<= 12 = (blank)
6	1001	10/1/2022	5	0		<= 12 = (blank)
7	1001	11/1/2022	6	0		<= 12 = (blank)
8	1001	12/1/2022	7	0		<= 12 = (blank)
9	1001	1/1/2023	8	0		<= 12 = (blank)
10	1001	2/1/2023	9	0		<= 12 = (blank)
11	1001	3/1/2023	10	0		<= 12 = (blank)
12	1001	4/1/2023	11	0		<= 12 = (blank)
13	1001	5/1/2023	12	1		<= 12 = (blank)
14	1001	6/1/2023	13	0	2	=SUM(D2:D13)
15	1001	7/1/2023	14	0	2	=SUM(D3:D14)
16	1001	8/1/2023	15	0	2	=SUM(D4:D15)
17	1001	9/1/2023	16	0	1	=SUM(D5:D16)
18	1001	10/1/2023	17	0	1	=SUM(D6:D17)
19	1001	11/1/2023	18	0	1	=SUM(D7:D18)
20	1001	12/1/2023	19	0	1	=SUM(D8:D19)
21	1001	1/1/2024	20	0	1	=SUM(D9:D20)
22	1001	2/1/2024	21	0	1	=SUM(D10:D21)
23	1001	3/1/2024	22	0	1	=SUM(D11:D22)
24	1001	4/1/2024	23	0	1	=SUM(D12:D23)
25	1001	5/1/2024	24	0	1	=SUM(D13:D24)
26	1001	6/1/2024	25	0	0	=SUM(D14:D25)
27	1001	7/1/2024	26	0	0	=SUM(D15:D26)
28	1002	5/1/2018	1	0		<= 12 = (blank)
29	1002	6/1/2018	2	0		<= 12 = (blank)
30	1002	7/1/2018	3	0		<= 12 = (blank)
31	1002	8/1/2018	4	0		<= 12 = (blank)
32	1002	9/1/2018	5	0		<= 12 = (blank)
33	1002	10/1/2018	6	0		<= 12 = (blank)
34	1002	11/1/2018	7	0		<= 12 = (blank)
35	1002	12/1/2018	8	0		<= 12 = (blank)
36	1002	1/1/2019	9	0		<= 12 = (blank)
37	1002	2/1/2019	10	1		<= 12 = (blank)
38	1002	3/1/2019	11	0		<= 12 = (blank)
39	1002	4/1/2019	12	1		<= 12 = (blank)
40	1002	5/1/2019	13	0	2	=SUM(D28:D39)
41	1002	6/1/2019	14	0	2	=SUM(D29:D40)
42	1002	7/1/2019	15	0	2	=SUM(D30:D41)
43	1003	12/1/2020	1	0		<= 12 = (blank)
44	1003	1/1/2021	2	0		<= 12 = (blank)
45	1003	2/1/2021	3	0		<= 12 = (blank)
46	1003	3/1/2021	4	0		<= 12 = (blank)
47	1003	4/1/2021	5	0		<= 12 = (blank)
48	1003	5/1/2021	6	0		<= 12 = (blank)
49	1003	6/1/2021	7	0		<= 12 = (blank)
50	1003	7/1/2021	8	0		<= 12 = (blank)
51	1003	8/1/2021	9	0		<= 12 = (blank)
52	1003	9/1/2021	10	0		<= 12 = (blank)
53	1003	10/1/2021	11	0		<= 12 = (blank)
54	1003	11/1/2021	12	1		<= 12 = (blank)
55	1003	12/1/2021	13	0	1	=SUM(D43:D54)
56	1003	1/1/2022	14	0	1	=SUM(D44:D55)
57	1003	2/1/2022	15	0	1	=SUM(D45:D56)
58	1003	3/1/2022	16	0	1	=SUM(D46:D57)
59	1003	4/1/2022	17	0	1	=SUM(D47:D58)
60	1003	5/1/2022	18	0	1	=SUM(D48:D59)

@ppm1, if you have any advice for this, that'd be terrific.  I should also say that when I tried to use your code before, I kept getting the error message that "We cannot convert a value of type Table to type list."  So you can see that I didn't get very far.  Thanks!

[jgeddes]

@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]

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.)

[wdx223_Daniel]

= #table(Table.ColumnNames(PreviousStepName)&{"Removals L12M"},List.Accumulate(Table.ToRows(PreviousStepName),{{},{},""},(x,y)=>let a=List.Skip(x{1}&{y},each _{0}<Date.AddMonths(y{0},-12)) in if x{2}=y{1} then {x{0}&{y&{List.Sum(List.Zip(a){2})}},a,y{1}} else {x{0}&{y&{y{2}}},{y},y{1}}){0})

[ppm1 · Accepted solution]

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

[JamesRobson]

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 Date	Component	Monthly Removals	Removals L12M
09/01/2021	A	1	8
12/01/2021	A	2	8
03/01/2022	A	5	8
05/01/2022	A	0	8

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]

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.