3 month rolling - new column (not measure) in power query

[IF]

Hi,

I checked the forum before writing this question, but I couldn't find an answer. I want to have a new column in power query editor. I don't want a measure. is it possible to have two more columns to see the 3M data. I have the following example data:

Date	Month
04.2022	2
03.2022	3
02.2022	4
01.2022	4
12.2021	5
11.2021	3
10.2021	1

I would like to see it as in power query:

Date

Date	Value	3M -current+previous+preprevious	3M - previous + preprevious + 4 months ago
04.2022	2	9	11
03.2022	3	11	13
02.2022	4	13	12
01.2022	4	12	9
12.2021	5	9	4
11.2021	3	4	1
10.2021	1	1

I know how to write the dax, i am interested in only the new column in power query.

THanks in advance.

[v-kkf-msft · Accepted solution]

Hi @IF ,

Please create the following custom columns:

3M -current+previous+preprevious =
let 
   myfunction = (CurrentIndex) => 
     let 
       IndexList = {[Index]-2..[Index]}
     in 
       IndexList,
   Data = List.Sum(Table.SelectRows(#"Added Custom",each List.Contains(myfunction([Index]),[Index]))[Month])

in 
   Data

3M - previous + preprevious + 4 months ago =
let 
   myfunction = (CurrentIndex) => 
     let 
       IndexList = {[Index]-3..[Index]-1}
     in 
       IndexList,
   Data = List.Sum(Table.SelectRows(#"Added Custom",each List.Contains(myfunction([Index]),[Index]))[Month])

in 
   Data

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

= #table(Table.ColumnNames(Source)&{"3M -current+previous+preprevious","3M - previous + preprevious + 4 months ago"},List.Accumulate(List.Reverse(Table.ToRows(Source)),{{},{}},(x,y)=>let a={y{1}}&x{1} in {{y&List.Transform({0,1},each List.Sum(List.Range(a,_,3)))}&x{0},List.FirstN(a,3)}){0})

[IF]

Hi,

Thanks for the quick answer. I tried it, but it is populating the rows, which is making a problem for other calculations. Below is the screenshot. Maybe I do smth wrong. As you see the 04.2022 row is populated 8 times, which should be only one row.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcvBCQAgDATBXu4t4p2xmpD+21AS8vE5LOsOm1oSBoQYjt3cSTUtyY+Z+XzKbNfN1SYiLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type number}, {"Month", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "3M -current+previous+preprevious", each List.Sum(List.Range(#"Added Index"[Month],[Index],3))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "3M - previous + preprevious + 4 months ago", each try #"Added Custom"[#"3M -current+previous+preprevious"]{[Index]+1} otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"})
in
    #"Removed Columns"

[IF]

Hi,

Thanks for the quick answer. I already use an index but vice versa. 10.2021 is 0 and 4.2022 is 6. I tried to use -3 but didn't work. Can I use -3 with this logic?

  #"Added Custom" = Table.AddColumn(#"Added Index", "3M -current+previous+preprevious", each

List.Sum(List.Range(#"Added Index"[Month],[Index],-3))),

Regards

[Vijay_A_Verma]

In both, Index has to be replaced with 6-[Index]. If you want to dynamically determine this number 6 i.e. highest, then you replace 6 with List.Max(#"Added Index"[Index])

Hence, below is the revised query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcvBCQAgDATBXu4t4p2xmpD+21AS8vE5LOsOm1oSBoQYjt3cSTUtyY+Z+XzKbNfN1SYiLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type number}, {"Month", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 6, -1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "3M -current+previous+preprevious", each List.Sum(List.Range(#"Added Index"[Month],6-[Index],3))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "3M - previous + preprevious + 4 months ago", each try #"Added Custom"[#"3M -current+previous+preprevious"]{7-[Index]} otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"})
in
    #"Removed Columns"

[IF]

Sorry to say that I have repeated values. It really depends on the month. it is sometimes 10 sometimes 20. I have also some other columns. In this regards, I should put the index on month. My purpose is to have 3 month rolling data. I tried and it didn't work in this case. Here is an example: