Non- Moving Average with Slicer

[lherbert501]

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

[v-jianboli-msft · Accepted solution]

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.

[lherbert501]

Hi @v-jianboli-msft ,

If the "Outstanding" was a measure and not in a column, would this still work or change at all?

Thanks

Liam

[lherbert501]

Thankyou @v-jianboli-msft 

[amitchandak]

@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]

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

[lherbert501]

hi @amitchandak ,

Is this possible?

Thanks so much in advance

Liam