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
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
PBI project_Rolling Measures.pbix935 KB

tackytechtom

Most Valuable Professional

3 years ago

Hi Kleine285 ,

How about this:

And here the code:

RollingMeasure = 
VAR _prevYear = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1
RETURN
( 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[Value] ), 
        ALL   ( 'Table' ),
        YEAR  ( 'Table'[Date] ) = _prevYear, 
        MONTH ( 'Table'[Date] ) = 12
    ) + 
    SELECTEDVALUE ( 'Table'[Value] ) 
) / 2

Let me know if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/