Forum Discussion
Year On year comparisons
Hi
I have a table showing bookings for each month this year and last year.
I have a second table showing YOY differences where the YOY difference is calculated by
YoY_diff =
VAR CurrentDate = max('exptemp fullyon'[date])
var lastyear = CALCULATE('data1'[Sum_Bookings], 'exptemp fullyon'[foyear] = YEAR(CurrentDate) - 1 )
var thisyear = CALCULATE('data1'[Sum_Bookings], 'exptemp fullyon'[foyear] = YEAR(CurrentDate) )
RETURN CALCULATE(thisyear / lastyear)-1
The datasource has data for each day. The problem is that the YOY table has the correct YOY difference for 2017 but it forces a row for 2016 with a row of infinities. Presumably because it's trying to calculate 2016 vs 2015 when 2015 data isn't in the dataset. If I try and filter out the 2016 row because I don't want to see it the 2017 row then goes to all infinities.
I have tried amending to RETURN CALCULATE IFERROR((thisyear / lastyear)-1, blank()) but this errors and even so I would still rather remove this row from sight. Can I simply hide the 2016 row? I can't see a way and I struggle to believe YOY comparisons should be difficult to do when they're the most common metric to look at.
Hi Stroop,
According to your description above, using DIVIDE function in your formula like below should work in this scenario. :smileyhappy:
YoY_diff = VAR CurrentDate = MAX ( 'exptemp fullyon'[date] ) VAR lastyear = CALCULATE ( 'data1'[Sum_Bookings], 'exptemp fullyon'[foyear] = YEAR ( CurrentDate ) - 1 ) VAR thisyear = CALCULATE ( 'data1'[Sum_Bookings], 'exptemp fullyon'[foyear] = YEAR ( CurrentDate ) ) RETURN DIVIDE ( thisyear - lastyear, lastyear )Regards
2 Replies
- v-ljerr-msft
Microsoft Employee
Hi Stroop,
According to your description above, using DIVIDE function in your formula like below should work in this scenario. :smileyhappy:
YoY_diff = VAR CurrentDate = MAX ( 'exptemp fullyon'[date] ) VAR lastyear = CALCULATE ( 'data1'[Sum_Bookings], 'exptemp fullyon'[foyear] = YEAR ( CurrentDate ) - 1 ) VAR thisyear = CALCULATE ( 'data1'[Sum_Bookings], 'exptemp fullyon'[foyear] = YEAR ( CurrentDate ) ) RETURN DIVIDE ( thisyear - lastyear, lastyear )Regards
- StroopFrequent Visitor
That's worked perfectly, thanks.