Forum Discussion

JonathanJohns's avatar
JonathanJohns
Icon for Helper III rankHelper III
9 years ago

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 d...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
JonathanJohns
Icon for Helper III rankHelper III
9 years ago

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 :

 

WeekAverageIndexRunning Average
0017-W0960,3741711NA
0017-W1060,3221052NA
0017-W1160,224279360,30685167
0017-W1260,26567460,27068467
0017-W1360,153548560,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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      JonathanJohns
      Icon for Helper III rankHelper III
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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