Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare last year with same period this year

Hello everyone, 

 

I want to compare the percentual changes per year. For 2021, this means I want to compare January till May 2021 with January till May 2020. For the years before that, I just want to compare the whole year with each other. 

 

I used and IF-statement for my measures, but the year 2021 didn't show up. The measures are as follows:

 

YTD = IF(MAX(Kalender[Datum]) <= TODAY(),
CALCULATE([Kosten], DATESYTD(Kalender[Datum])))
 
YTD LY = IF(MAX(Kalender[Datum]) <= TODAY(), CALCULATE([YTD], SAMEPERIODLASTYEAR(Kalender[Datum])))
 
percentual change = ([YTD]-[YTD LY])/[YTD LY]

 

 

ytd.png

 

Could someone help me with getting a percentual return for 2021 as well? 

1 ACCEPTED SOLUTION

@Anonymous ,

 

OK. Here's the measures:

_kosten = SUM(__Finance[BedragKostenWerkelijk])

_kostenPY = 
CALCULATE(
    [_kosten],
    SAMEPERIODLASTYEAR(__Calendar[Datum])
)

// This is the one you want for your YoY %:
_kostenPYtoday = 
CALCULATE(
    [_kostenPY],
    FILTER(
        __Calendar,
        __Calendar[Datum] <= TODAY()
    )
)

_kostenChangeYoY = ([_kosten] - [_kostenPYtoday]) / [_kostenPYtoday]

 

These give me the following output:

BA_Pete_2-1624028655753.png

 

You don't need the YTD function at all for your required output.

 

Sorry it took so long! 🙂

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

12 REPLIES 12
BA_Pete
Super User
Super User

Hi @Anonymous ,

 

Can you try removing the following argument from your measures please?

IF(MAX(Kalender[Datum]) <= TODAY()

 

I think this could be causing your issue as the max calendar date in 2021 is 31st December, which is not less than today's date.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

Hi @BA_Pete 

 

Thanks for your reply! 

I removed it from my two measures. Unfortunately, 2021 till May is compard with 2020, so all of the months. The percentage is -55,2%, which doesn't say anything at all of course. 

@Anonymous ,

 

No problem. Try these measures instead:

 

YTD = 
CALCULATE(
  [Kosten],
  DATESYTD(Kalender[Datum])
)
 
YTD PY = 
IF(
  ISBLANK([YTD]),
  BLANK(),
  CALCULATE(
    [YTD],
    SAMEPERIODLASTYEAR(Kalender[Datum])
  )
)

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

ytd hulp pbi.png

 

The result stays the same...

@Anonymous ,

 

Ok. I think this is because the YTD function populates future dates until the end of the end year in the background. Try this instead:

 

YTD PY = 
IF(
  ISBLANK([Kosten]),
  BLANK(),
  CALCULATE(
    [YTD],
    SAMEPERIODLASTYEAR(Kalender[Datum])
  )
)

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

@BA_Pete 

The result is still the same.

 

Would it be helpful for you if I give you some data?

AAARGH!

 

I'm looking at your required output again and, for this visual, you don't need the YTD function at all, you can just use the sum of [Kosten] this year and sum of [Kosten] prior year.

 

Are there other visuals that you want to specifically show YTD growth (e.g. charts of some sort), or do you just want to produce this table?

 

Either way, I would change how your measures are set up as follows:

Kosten = SUM(yourTable[Kosten])

KostenPY = 
CALCULATE(
  [Kosten],
  SAMEPERIODLASTYEAR(Kalendar[Datum])
)

KostenYTD =
CALCULATE(
  [Kosten],
  DATESYTD(Kalendar[Datum])
)

KostenPYTD =
CALCULATE(
  [KostenPY],
  DATESYTD(Kalendar[Datum])
)

KostenPYTToday =
IF(
  ISBLANK([Kosten],
  BLANK(),
  [KostenPYTD]
)
  

 

If that doesn't work then, yes, I will need some data please, and a wall to bang my head on! 🙂

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

@BA_Pete 

Hahahaha, I do hope you have soft walls :p. Cause this is the result:

 

4 metingen.png

 

 I just want to show this table yeah. 

 

What kind of data do you need? I assume our Date table and the Financial table, where all the Kosten (Costs) are stored?  

@Anonymous ,

 

Yes, date table and cost table please. Make sure any sensitive information is removed first as well.

I can't believe I'm struggling with this one, should be so simple 😞

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

@BA_Pete Hahah it's okay. I hope the data will help. 

The Finance and Calendar table are connected on DatumBoeking and Datum (both column A in the two sheets). 

 

You can find the data by using the link:

 

https://drive.google.com/file/d/1QPhniy8BOGro4HU054UjYIuFzOB840jQ/view?usp=sharing

 

 

@Anonymous ,

 

OK. Here's the measures:

_kosten = SUM(__Finance[BedragKostenWerkelijk])

_kostenPY = 
CALCULATE(
    [_kosten],
    SAMEPERIODLASTYEAR(__Calendar[Datum])
)

// This is the one you want for your YoY %:
_kostenPYtoday = 
CALCULATE(
    [_kostenPY],
    FILTER(
        __Calendar,
        __Calendar[Datum] <= TODAY()
    )
)

_kostenChangeYoY = ([_kosten] - [_kostenPYtoday]) / [_kostenPYtoday]

 

These give me the following output:

BA_Pete_2-1624028655753.png

 

You don't need the YTD function at all for your required output.

 

Sorry it took so long! 🙂

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

Thank you, Pete! This does work! Thank you for all your effort 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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