Error in Rolling 12 month average

[arnabmit]

Hi,

I was trying to replicate Alberto Ferrari's method of Rolling 12 month average (Average of -13 to -1 month), but it is not matching with the manual calculation. Any idea why it is so?

My regular calendar table is _Calendar, and the disconnected calendar table with inactive relationship is '12m Cal'

Avg Rolling 12M = 
VAR RefDate = MAX(_Calendar[Date])
VAR PrevDates = 
    DATESINPERIOD(
        '12m Cal'[Date],
        EDATE(RefDate,-1),
        -12,
        MONTH
    )
VAR Result = 
    CALCULATE(
        DIVIDE([MTD],12),
        REMOVEFILTERS('_Calendar'),
        KEEPFILTERS(PrevDates),
        USERELATIONSHIP('12m Cal'[Date], _Calendar[Date])
    )
RETURN
    Result

Excel file 

[amitchandak · Accepted solution]

@arnabmit , Check the suggestion from SpartaBI. Try like

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

[SpartaBI]

@arnabmit I'm not sure why you have another disconnected calendar table. 
You can PM me and we can take a look together at your model in zoom if you want

[arnabmit]

It is as per Alberto Ferarri's method here https://www.youtube.com/watch?v=ACvYaXnpyCM