Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys, i need help with DAX calculating % growth of profits over period of, let's say, 5 years. As you will see in table below i also have different work sectors(currently 1 in table) and i want to show in table visual top 3 work sectors with fastest growing profits over a period of 5 years.
Work Sector | Year | Yearly net profit(€) |
Acommodation and food services | 2019 | € 294 |
Acommodation and food services | 2020 | € 315 |
Acommodation and food services | 2021 | € 339 |
Acommodation and food services | 2022 | € 392 |
Acommodation and food services | 2023 | € 440 |
Acommodation and food services | 2024 | € 503 |
Solved! Go to Solution.
Hi @IgorKaradzic ,
Create a separate year column as a slicer. Then create the following formula:
result =
VAR sel_ =
SELECTEDVALUE ( 'Date'[Year] )
VAR maxProfit =
CALCULATE ( MAX ( 'Table'[Yearly net profit(€)] ), 'Table'[Year] = sel_ + 5 )
VAR minProfit =
CALCULATE ( MAX ( 'Table'[Yearly net profit(€)] ), 'Table'[Year] = sel_ )
VAR per_ =
DIVIDE ( maxProfit - minProfit, minProfit )
RETURN
per_
1 =
IF ( RANKX ( ALL ( 'Table'[Work Sector] ), [result],, DESC ) <= 3, 1, 0 )
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @IgorKaradzic ,
Create a separate year column as a slicer. Then create the following formula:
result =
VAR sel_ =
SELECTEDVALUE ( 'Date'[Year] )
VAR maxProfit =
CALCULATE ( MAX ( 'Table'[Yearly net profit(€)] ), 'Table'[Year] = sel_ + 5 )
VAR minProfit =
CALCULATE ( MAX ( 'Table'[Yearly net profit(€)] ), 'Table'[Year] = sel_ )
VAR per_ =
DIVIDE ( maxProfit - minProfit, minProfit )
RETURN
per_
1 =
IF ( RANKX ( ALL ( 'Table'[Work Sector] ), [result],, DESC ) <= 3, 1, 0 )
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, it worked.
Please try to use a Quick Measure first. It has Year Over Year examples.
Remember to add a calendar table to the data model.
Tried it and it also worked.Thank you for suggesting it.
User | Count |
---|---|
63 | |
46 | |
19 | |
18 | |
15 |
User | Count |
---|---|
119 | |
41 | |
40 | |
28 | |
22 |