Running Average for three weeks

[JonathanJohns]

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.

[Anonymous]

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 Zhang

[JonathanJohns]

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

[Anonymous]

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

[JonathanJohns]

I have done like you said. I created a new table with the DAX, then created the colum"Index" and finally the column "Running Average" and I got the last table.

[Anonymous]

Hi @JonathanJohns,

The formulas work as expected in my scenario, could you please share me all the data in  your original table so that I can  test?



Thanks,
Lydia Zhang

[JonathanJohns]

How can I do to send you my data please ?

[JonathanJohns]

I finally did it ! It was just a little error in the DAX. I get the table I want.

Thanks for your help.

[Anonymous]

Hi @JonathanJohns,

I note that you post another thread for the above new issue, please review Qiuyun's reply and your thread will be tracked in that thread.

Thanks,
Lydia Zhang

[Greg_Deckler]

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.

[JonathanJohns]

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 ?