Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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))
@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))
@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
It is as per Alberto Ferarri's method here https://www.youtube.com/watch?v=ACvYaXnpyCM
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |