topic Re: Rolling average last 3 rows in DAX Commands and Tips

Rolling average last 3 rows

Anonymous — Mon, 04 Oct 2021 10:54:08 GMT

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 I don't know what DAX formula I need to use.

I think I should use something based on nonblank rows for the W-kee column but I really have no glue.

Please help.

Re: Rolling average last 3 rows

VahidDM — Mon, 04 Oct 2021 11:39:11 GMT

Hi Anonymous

Try this measure:

Measure= Var _MaxL = calculate(max(table[SPR]),filter(table,table[W-kee]<>"")) Var _MinL = _MaxL-2 return Calculate(avarage(table[W-kee]),filter(table,table[SPR]>=_MinL &&table[SPR]<=_MaxL ))

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

Appreciate your Kudos!!

Re: Rolling average last 3 rows

Tanushree_Kapse — Mon, 04 Oct 2021 11:44:18 GMT

Hi Anonymous ,

var Rolling Average= CALCULATE(Table[W-knee], FILTER(ALLSELECTED(Table[SPR]), ISONORAFTER( Table[SPR], MAX(Table[SPR]), asc)))
Return (Rolling Average/ 3)

Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks

Re: Rolling average last 3 rows

Anonymous — Mon, 04 Oct 2021 12:41:36 GMT

Re: Rolling average last 3 rows

Anonymous — Mon, 04 Oct 2021 12:52:49 GMT

Re: Rolling average last 3 rows

VahidDM — Mon, 04 Oct 2021 23:48:04 GMT

Hi Anonymous

What is the format of [SPR] column?

Appreciate your Kudos!!

Re: Rolling average last 3 rows

Anonymous — Tue, 05 Oct 2021 05:56:19 GMT

Whole number

Re: Rolling average last 3 rows

VahidDM — Tue, 05 Oct 2021 07:25:27 GMT

Hi Anonymous

Can you share your PBIX file with us (After removing sensetive data)?

Appreciate your Kudos!!

Re: Rolling average last 3 rows

v-robertq-msft — Tue, 26 Oct 2021 08:13:05 GMT

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.

Re: Rolling average last 3 rows

Anonymous — Tue, 26 Oct 2021 13:49:24 GMT

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

Much appreciated!