Forum Discussion

lherbert501's avatar
lherbert501
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

6 Month Average Overall without Slicer

Hi there,   I have a measure that calculates the rolling average over 6 months which works great as below.     In my dataset it only contains 6 months so it will cut out e.g. February when...
Need Help
  • lherbert501's avatar
    lherbert501
    4 years ago

    Managed to do this with the ALL function 🙂

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

lherbert501 , First of all, if you select data of month and you need value more than that, then you slicer on an independent date table 

 

date is joined and date 1 independent table

 

//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -6) +1
return
calculate( CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH)) , filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

 

  • lherbert501's avatar
    lherbert501
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    hi amitchandak 

     

    Thanks so much for the reply. 

    The new measure that you have wrote with an extra date table gives the same result as the rolling average below. 

    The column in yellow is what I'm aiming for if its possible. 6 Month overall average regardless of slicer change?

     

     

    Thanks

     

    Liam

    • lherbert501's avatar
      lherbert501
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Managed to do this with the ALL function 🙂