cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Microsoft Employee

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

2 REPLIES 2
Microsoft Employee

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

Frequent Visitor

That's worked perfectly, thanks.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors