Month on Month Rolling Avg

[KG1]

Hi

I have the following measure

I need to able to show month on month rolling avg in a visual

The total 1.2 is correct for the last 12 months - how do I show this month on month rather than just actuals for each month

Thank you in advance

[KG1 · Accepted solution]

Sorry - I cant get it to work 😞

Rolling 12 =

var _max = maxx(ALLSELECTED(SHEQ[Month]),SHEQ[Month]) // or today()

var _min = date(year(_max), month(_max)-12,1)

return

CALCULATE(AverageX(values('SHEQ'[Month]) , [NLTI Calc]) ,filter(SHEQ, SHEQ[Month] <=_max && SHEQ[Month] >=_min) , allselected() )

[v-kkf-msft · Accepted solution]

Hi @KG1 ,

Please try the following formula:

Measure = 
CALCULATE (
    [Total NLTI's] / [Total Working Hours] * 10 ^ 5,
    FILTER ( ALLSELECTED ( SHEQ ), SHEQ[Month] <= MAX ( SHEQ[Month] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KG1]

Great  - thank you - that worked perfectly

[amitchandak]

@KG1 , try like

Rolling 12 =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(AverageX(values('Date'[Month Year]) , [NLTI Calc]) ,filter(date, date[date] <=_max && date[date] >=_min))

and for Gt in all rows

Rolling 12 GT =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(AverageX(values('Date'[Month Year]) , [NLTI Calc]) ,filter(date, date[date] <=_max && date[date] >=_min) , allselected() )

[KG1 · Accepted solution]

Sorry - I cant get it to work 😞

Rolling 12 =

var _max = maxx(ALLSELECTED(SHEQ[Month]),SHEQ[Month]) // or today()

var _min = date(year(_max), month(_max)-12,1)

return

CALCULATE(AverageX(values('SHEQ'[Month]) , [NLTI Calc]) ,filter(SHEQ, SHEQ[Month] <=_max && SHEQ[Month] >=_min) , allselected() )