DAX formula for calculating % growth of profits over period of 5 years

[IgorKaradzic]

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

[Anonymous · Accepted 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.

[IgorKaradzic]

Thank you, it worked.

[lbendlin]

Please try to use a Quick Measure first. It has Year Over Year examples.  

Remember to add a calendar table to the data model.

[IgorKaradzic]

Tried it and it also worked.Thank you for suggesting it.