topic Re: DAX formula for calculating % growth of profits over period of 5 years in DAX Commands and Tips

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

IgorKaradzic — Tue, 21 May 2024 08:49:25 GMT

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

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

lbendlin — Wed, 22 May 2024 00:37:07 GMT

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

Remember to add a calendar table to the data model.

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

Anonymous — Wed, 22 May 2024 02:38:38 GMT

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.

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

IgorKaradzic — Fri, 24 May 2024 10:10:48 GMT

Thank you, it worked.

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

IgorKaradzic — Fri, 24 May 2024 10:11:39 GMT

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