Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
First I would write two measures, one for Realised and one for Forcasted:
Then you can use some variables and your new measure to calculate the ratio. I believe the problem you were running into is your date table was following the filter context when you picked a month becasue you had not opened it up in your measure.
Avg 12 months = VAR MaxDay = MAX ( 'Date'[Date] ) VAR EarliestDay = EOMONTH(MaxDay,-12)+1 RETURN CALCULATE( DIVIDE ( [RealisedCostSavings] , [ForecastedCostSavings] , 0) , FILTER( ALL ( 'Date' ), 'Date'[Date] >= EarliestDay && 'Date'[Date] <= MaxDay ) )
The variables return the last date you have seleted (based on your slicer) and the first day of the month 11 months ago so you get a full 12 month window then those are used to filter the date table inside the CALCULATE
First I would write two measures, one for Realised and one for Forcasted:
Then you can use some variables and your new measure to calculate the ratio. I believe the problem you were running into is your date table was following the filter context when you picked a month becasue you had not opened it up in your measure.
Avg 12 months = VAR MaxDay = MAX ( 'Date'[Date] ) VAR EarliestDay = EOMONTH(MaxDay,-12)+1 RETURN CALCULATE( DIVIDE ( [RealisedCostSavings] , [ForecastedCostSavings] , 0) , FILTER( ALL ( 'Date' ), 'Date'[Date] >= EarliestDay && 'Date'[Date] <= MaxDay ) )
The variables return the last date you have seleted (based on your slicer) and the first day of the month 11 months ago so you get a full 12 month window then those are used to filter the date table inside the CALCULATE
Very kind of you sir!! Super appreciated for taking the time. I'll give that a bash and let you know how it goes! 🙂
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |