Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling average last 3 rows

Hi, I'm new here and I need some help. I would like to calculate a "W-kee" rolling average over the last 3 rows in the table example as below:     So the result should be ((1+4+1)/3=2), but...
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, you can try this measure to get the expected result:

    Rolling average =

var _lastrow=CALCULATE(MIN('Table'[SPR]),FILTER(ALL('Table'),[W-kee]=BLANK()&&[W-def]=BLANK()&&[W-mid]=BLANK()&&[W-att]=BLANK()))

return

CALCULATE(AVERAGE('Table'[W-kee]),FILTER(ALL('Table'),[SPR]<=_lastrow-1&&[SPR]>=_lastrow-3))

     

    And you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-robertq-msft's avatar
v-robertq-msft
Community Support
4 years ago

Hi, 

According to your description, I can roughly understand your requirement, you can try this measure to get the expected result:

Rolling average =

var _lastrow=CALCULATE(MIN('Table'[SPR]),FILTER(ALL('Table'),[W-kee]=BLANK()&&[W-def]=BLANK()&&[W-mid]=BLANK()&&[W-att]=BLANK()))

return

CALCULATE(AVERAGE('Table'[W-kee]),FILTER(ALL('Table'),[SPR]<=_lastrow-1&&[SPR]>=_lastrow-3))

 

And you can get what you want, like this:

 

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Great, that's the one I'm looking for.

Much appreciated!