Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Could someone help me with getting a percentual return for 2021 as well?
Solved! Go to 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:
You don't need the YTD function at all for your required output.
Sorry it took so long! 🙂
Pete
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Hahahaha, I do hope you have soft walls :p. Cause this is the result:
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
Proud to be a Datanaut!
@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:
You don't need the YTD function at all for your required output.
Sorry it took so long! 🙂
Pete
Proud to be a Datanaut!
Thank you, Pete! This does work! Thank you for all your effort 🙂
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |