Year On year comparisons

[Stroop]

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.

[v-ljerr-msft · Accepted solution]

Hi @Stroop,

According to your description above, using DIVIDE function in your formula like below should work in this scenario. 

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

[Stroop]

That's worked perfectly, thanks.