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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

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.

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.