Forum Discussion

Kleine285's avatar
Kleine285
New Member
3 years ago
Solved

Moving average

Hello everyone,    I've already looked at and tried some of your examples here, but they don't really help me. I need to calculate an average that keeps changing/shifting.   Example: Average of ...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Kleine285 

    I download this .pbix file , the filed in the visual is wrong, youo need to use the [Maand] filed in the 'Measures Mndst' table and [14.0] measure you created before not in the 'Table2'.

    For your need , you should update the measure to this :

    Moving average = var _date =SELECTEDVALUE('Mndst'[Maand])
var _this_year_month =MAXX( FILTER( 'Table 2' , [Maand] = DATE( YEAR( _date)  ,MONTH(_date),1)) , [14.0])
var _last_year_dec = MAXX( FILTER( 'Table 2' , [Maand] = DATE( YEAR( _date) -1 ,12,1)) , [14.0])
 return
IF(_last_year_dec=BLANK(),BLANK(),DIVIDE(_last_year_dec+_this_year_month,2))

    The result is follows , i think that is your need :

     

    Best Regards,

    Aniya Zhang

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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Kleine285 

I download this .pbix file , the filed in the visual is wrong, youo need to use the [Maand] filed in the 'Measures Mndst' table and [14.0] measure you created before not in the 'Table2'.

For your need , you should update the measure to this :

Moving average = var _date =SELECTEDVALUE('Mndst'[Maand])
var _this_year_month =MAXX( FILTER( 'Table 2' , [Maand] = DATE( YEAR( _date)  ,MONTH(_date),1)) , [14.0])
var _last_year_dec = MAXX( FILTER( 'Table 2' , [Maand] = DATE( YEAR( _date) -1 ,12,1)) , [14.0])
 return
IF(_last_year_dec=BLANK(),BLANK(),DIVIDE(_last_year_dec+_this_year_month,2))

The result is follows , i think that is your need :

 

Best Regards,

Aniya Zhang

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

  • Kleine285's avatar
    Kleine285
    New Member
    3 years ago

    Thank you all, especially Aniya Zhang for your help.

  • Kleine285's avatar
    Kleine285
    New Member
    3 years ago

    Hi Aniya Zhang,

     

    I would like your help regarding the measure you provided. It seems that I can't filter on institution "Instelling". 

     

    How can I make the "Moving Average" measure also work when filtering by insitution.

     

    Thanks in advance for your help.

     

    Isstvan