Average of Past Months for future Value

[Gladiator909]

Hi there,

I have the following table:

Date	Value
2022/Jan	1
2022/Feb	2
2022/Mar	3
2022/Apr	5
2022/May	2
2022/Jun	6
2022/Jul	1
2022/Aug	23
2022/Sep	5
2022/Oct	6
2022/Nov	8
2022/Dec	2
2023/Jan	3
2023/Feb	5
2023/Mar	1

I now want to predict the value for the months coming affter March. That is predict the value for april, may, june to december. And I need to do this using the average of the previous months.

So in Excel, if I want to find 2023/Apr = Average(B15:B17)

Now for the subsequent months, I just drag the edge of the box all the way down and it does the average for me.

So average for 2023/May = AVERAGE(B16:B18) (Note: it includes B18 that was previously calculated)

I am trying to find a way to do that in DAX or Power Query inside Power BI but I can't seem to find a way.

Any ideas?

Thanks

[CNENFRNL · Accepted solution]

Hard to implement logic of such recursive calculation by DAX; but lightweight for PQ.

let
    n = 9,
    Source = {3,5,1},
    #"Rolling Avg" = List.Accumulate({4..n+3}, Source, (s,c) => s & {List.Average(List.LastN(s, 3))}),
    ToTable = Table.FromColumns({List.Transform({1..n+3}, each Date.ToText(Date.AddMonths(#date(2023,1,1),_-1), "yyyy MMM")), #"Rolling Avg"}, {"Yr Mth", "Value"})
in
    ToTable

[Jihwan_Kim]

Hi,

One of ways to achieve this is,

1. create a function in Power Query Editor like below.

2. and then follow the advanced editor like below.

Please check the attached pbix file.

Thank you.