Forum Discussion

Stroop's avatar
Stroop
Frequent Visitor
9 years ago
Solved

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's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft 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

    • Stroop's avatar
      Stroop
      Frequent Visitor

      That's worked perfectly, thanks.