Forum Discussion

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

Non- Moving Average with Slicer

Hi there,

 

I have my measure below but its giving me a moving average instead of the 6 months static.

 

I am trying to get the Result column in the second image below but for only 6 months, so it doesn't change when the slicer date changes. Is this possible?

 

testy =
var NumOfMonths = 6
var LastSelectedDate = MAX(RS_Dates[DateFull])
var Period = DATESINPERIOD(RS_Dates[DateFull], LastSelectedDate,  -NumOfMonths,  MONTH)
Var Result =
CALCULATE(
AVERAGEX(
    VALUES(RS_Dates[PeriodCal]),
    [Outstanding]),Period, ALL(Sales))
return
Result

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi lherbert501,

     

    Please try:

    Average = 
var _a = MAXX('RS_Dates',[DateFull])
return CALCULATE(AVERAGEX(ALL('Table'),[Outstanding]),DATESINPERIOD('RS_Dates'[DateFull],_a,-6,MONTH))

    Final output:

    Best Regards,

    Jianbo Li

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

6 Replies

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

    lherbert501 , You have try a measure like this example

     

    6 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
    ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

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

      Hi amitchandak 

       

      Unfortunately, this measure outputs the same as the running average, as displayed in the image.

       

      Would you have any idea how to get to the required result column??

       

      Thanks

       

      Liam

       

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi lherbert501,

     

    Please try:

    Average = 
var _a = MAXX('RS_Dates',[DateFull])
return CALCULATE(AVERAGEX(ALL('Table'),[Outstanding]),DATESINPERIOD('RS_Dates'[DateFull],_a,-6,MONTH))

    Final output:

    Best Regards,

    Jianbo Li

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