topic Re: Moving average of a full month in DAX Commands and Tips

Moving average of a full month

Anonymous — Mon, 10 May 2021 07:06:37 GMT

Hi everyone,

At the moment i have a problem with my moving average.

As you can see in the picture the moving average drops, because it count the month May. But the month May isn't over yet so it shouldn't be included in the moving average, because it gives a wrong image of the moving average. So i want to exclude the month we are in. I used the automatic formula that power bi provides;

Is there a way to exlude the current month in the moving average so it gives a right image of the moving average in three months?

Re: Moving average of a full month

amitchandak — Mon, 10 May 2021 08:00:20 GMT

Anonymous , in the last calculate, move all into a filter

and try like

filter(all(Kalendar]) , Kalendar[date]<=emonth(today(),-1)

Re: Moving average of a full month

Anonymous — Mon, 10 May 2021 09:40:10 GMT

Hi amitchandak

when i try to put in your exact part of the formula it gives a error ( is assume emonth =eomont because i cant find a dax with emont). First i copied your part:

FILTER(ALL(Kalender,Kalender[Datum]<=EOMONTH(TODAY(),-1)
but this give me a error that says ; The function Filter must have at least 2 arguments.

So i tried to fix this with this:

But then it gives a error at <=

and when i removed <= out of the formula:

FILTER(ALL(Kalender,Kalender[Datum]),EOMONTH(TODAY(),-1)

it says the following error: multiple table arguments are not allowed in the ALL/ ALLNOBLANK/REMOVEFILTERS function.

Any ideas how to solve this? thank you

Re: Moving average of a full month

PaulOlding — Mon, 10 May 2021 16:33:48 GMT

You've missed out the closing bracket for ALL function:

FILTER(ALL(Kalender)