Forum Discussion
Running Average for three weeks
Good afternoon,
I have a problem to do a formula with Power BI Desktop. I had a look on the forum to find my answer but I was not able to do the operation.
I have a table like this one with data from 01/02/2017 to 31/03/2017. I would like to calculate a running average for 3 weeks.
| DATE | Value | Day | Week | Month |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | 60,1 | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | 60,31 | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | 60,32 | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | 60,89 | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | NA | 0017-03-01 | 0017-W09 | 17-mars |
| 01/03/2017 | 59,89 | 0017-03-01 | 0017-W09 | 17-mars |
I know how to calculate an average for one week but I don't know how to do a running average. For an example I would like a table like that :
| Week | Average | Running Average |
| 0017-W09 | 60 | NA |
| 0017-W10 | 61 | NA |
| 0017-W11 | 62 | 61 |
| 0017-W12 | 60 | 61 |
| 0017-W13 | 61 | 61 |
| 0017-W14 | 61 | 60,666666 |
| 0017-W15 | 62 | 61,333333 |
With for the week 11 the calculation : (60+61+62)/3=61
For the week 12 : (61+62+60)=61
For the week 14 : (60+61+61)=60,66
...
I know it won't be possible to calculate for the 2 first weeks but do you know how to do for the others please ?
Thanks for your help.
10 Replies
Take a look at my article here:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
You will need to create another measure that takes the average of averages with an appropriate filter but this technique should work.
I read your article but I didn't understand how did it in my case... Sorry
How is it possible to calculate a running average for three weeks please ?
Hi JonathanJohns,
In your scenario, you can firstly create new table using the DAX below.
NewTable = SUMMARIZE('Datatable','Datatable'[Week],"Average",AVERAGE('Datatable'[Value]))Then create a index column in the new table using the following formula.
Index = CALCULATE(COUNT(NewTable[Week]),ALL(NewTable),FILTER(NewTable,NewTable[Week]<=EARLIER(NewTable[Week])))
And create Running Average using DAX below.Running Average = IF(NewTable[Index]=1 || NewTable[Index]=2,0,(NewTable[Average]+LOOKUPVALUE(NewTable[Average],NewTable[Index],NewTable[Index]-1)+LOOKUPVALUE(NewTable[Average],NewTable[Index],NewTable[Index]-2))/3)
Thanks,
Lydia ZhangHi Anonymous
Thanks for your answer. I have done the script you did and I got this table :
Concerning the column "Running Average", I wanted to know how it's possible to get a column like that please :
Week Average Index Running Average 0017-W09 60,374171 1 NA 0017-W10 60,322105 2 NA 0017-W11 60,224279 3 60,30685167 0017-W12 60,26567 4 60,27068467 0017-W13 60,153548 5 60,214499 The value 60,306851167 is the average of the 3 values from the weeks W09,W10,W11.
The value 60,27068467 is the average of the 3 values from the weeks W10,W11, W12.
The value 60,214499 is the average of the 3 values from the weeks W11,W12,W13.
I hope you know what I mean, I tried all my laste friday but it looks a little bit confused for me.
Thank you for your help.
Jonathan
Hi JonathanJohns,
Do you create newtable using the first formula? And do you right click the newTable and select "New Column" to apply the third formula?
Thanks,
Lydia Zhang
