Calculated Column Average Last Three Values

[grifdoogindoggy]

Hello, I am currently trying to find a way to average the last three values in a column. Currently, my Data Set looks like this:

Rank	Division	Fiscal Year	Participant Count
1	A	2020	14
2	A	2021	13
3	A	2022	12
4	A	2023
5	A	2024
6	A	2025
7	A	2026
8	A	2027
9	A	2028
10	A	2029
11	A	2030
12	B	2020	207
13	B	2021	196
14	B	2022	213
15	B	2023
16	B	2024
17	B	2025
18	B	2026
19	B	2027
20	B	2028
21	B	2029
22	B	2030

My desired Output looks like this:

Rank	Division	Fiscal Year	Participant Count	Projected
1	A	2020	14	14
2	A	2021	13	13
3	A	2022	12	12
4	A	2023		13
5	A	2024		12.66667
6	A	2025		12.55556
7	A	2026		12.74074
8	A	2027		12.65432
9	A	2028		12.65021
10	A	2029		12.68176
11	A	2030		12.66209
12	B	2020	207	207
13	B	2021	196	196
14	B	2022	213	213
15	B	2023		205.3333
16	B	2024		204.7778
17	B	2025		207.7037
18	B	2026		205.9383
19	B	2027		206.1399
20	B	2028		206.594
21	B	2029		206.2241
22	B	2030		206.3193

If Participant Count is not blank, then it will just return that value. However, if it is blank, then it takes the average of the last 3 values before it.

Thank you for any help or ideas!

[sturlaws]

Hi,

to solve this issue you have to create a recursive function in PowerQuery, it cannot be solved with DAX. 

A possible starting point for reading about recursion could be this blog post:
https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query/ 

You could also consider asking your question in the power query forum:
https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services 

Cheers,
Sturla