Forum Discussion

martipe1's avatar
martipe1
Helper II
2 years ago
Solved

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 ( ...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

martipe1's avatar
martipe1
Helper II
2 years ago

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 previously had my matrix 

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

Thanks in advance for your comments.

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Share the download link of the PowerBI file.

  • martipe1's avatar
    martipe1
    Helper II
    2 years ago

    Thank you again for your answer.

     

    Here it is an extract of my file Moving Average 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      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.

       

      • martipe1's avatar
        martipe1
        Helper II
        2 years ago

        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.