cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Moving Average

I need a moving average in the future and I expect to have a different value every month, the dax function I use is:

AVRG =
CALCULATE (
AVERAGEX ( KMR, KMR[MQTY] ),
DATESINPERIOD (
KMR[MRDTE],
MONTH(TODAY()) && YEAR(TODAY()),
3,
MONTH
)
)

And the end result is not what I expect.

As I only get one value, which is wrong,  for April,  and nothing for the rest of the months. I would expect for April (480+384+576)/3 (April + May+ June) = 480, for May should be (384+576+576)/3 = 512 and so on. And according to my dax function,  I think it should start in May

What am I doing wrong?

Thanks

1 ACCEPTED SOLUTION
Super User

You are welcome.  This measure works

``Measure = divide(calculate(sum(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),eomonth(min(calendar[date]),2))),3)``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Super User

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the MRdte column of the KMR table to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  This DAX pattern should work

Measure = calculate(average(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),edate(min(calendar[date]),3)))

I have asumed that mqty is a column in the kmr table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi, Ashish.

Thank you for your answer, I think I followed all the steps your recommended but no luck. Here are the details:

This is calendar table:

I created the relationship

I replaced the date column by the one in the calendar table

I created the measure

Measure = calculate(average(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),edate(min(calendar[date]),3)))
This is small example of my data:

I would expect first row for January and February the measure to be 0, for April (0+40+206)/3=82 , May (40+206+58)/3= 101.3  , and June (206+58+4)/3 = 89.3
But once I include the measure to my matrix I get some funny numbers

I honestly don't know what I'm doing wrong., my best guess is that I would need to change last part of the measure, but if that's the case, it's beyond my Power Bi knowledge

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Here it is an extract of my file Moving Average

Super User

You are welcome.  This measure works

``Measure = divide(calculate(sum(kmr[mQty]),datesbetween(calendar[date],min(calendar[date]),eomonth(min(calendar[date]),2))),3)``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you very much @Ashish_Mathur !!!

That did work perfectly.

I have another question regarding this measure, but will post it as another question and will tag you and mark this answer as accepted solution.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hey @martipe1 ,
you would like to have smtg like this a result, right?

You have two options. Depends on your requirements.
- write DAX (which will consider year change)
- write a visual calculation (considering only data visible in visual)

DAX (no visual calc):

``````Rolling Avg =
VAR Period =
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-3,
MONTH)

RETURN
CALCULATE(
AVERAGEX(
VALUES('Date'[Year Month Number]),
[Qty]),
Period)``````

Visual Calc:

``Rolling Avg (VC) = MOVINGAVERAGE([Qty], 3)``

Hope you got the idea.

Regards.

Helper I

It's almost what I'm looking for, let me elaborate on what I want to achieve.

The data I posted above is the forecast, I want to calculate the inventory turnover for the current month based on the average of the forecast for next three months, I need to see to the future. E.g. I have a forecast for May, June, and July of 10, 20, & 30 respectively, the average is 20 if I have an inventory of 45 my inventory turnover is 2.25 month.

I think the DAX you were so kind to share with me is almost the same as the one I created, because instead of -3 I consider +3 (looking into the future), still not getting the expected result.

Super User

ah, ok.
Let`s try to tweek the fomula.
I hope I get the idea right.

Formula used:

``````Rolling Avg (FC) =
VAR Period =
DATESINPERIOD(
'Date'[Date],
MIN('Date'[Date]),
3,
MONTH)

RETURN
IF([Qty] > 0,
CALCULATE(
AVERAGEX(
VALUES('Date'[Year Month Number]),
[Qty]),
Period)
)``````

Try out.

Regards

Helper I

Once again, thank you for your input
I tried to copy your DAX
and replace your columns by the columns I use, all are in the same table.
First part I replace your Date column by mine MRDTE which is a Date/Time type (Hierarchy), because I want to use it for incremental refresh. Any comment?
I have no problem replacing columns with the first part (VAR).
My problem starts with the IF functions as when I try to replace you Qty by mine MQTY, it doesn't show the column when I start typing.
What am I doing wrong?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.