Forum Discussion
Helper IIIRunning Average for three weeks
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
JonathanJohnsHi 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- 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.