topic Re: Excel Formula to Dax in DAX Commands and Tips

Excel Formula to Dax

Anonymous — Thu, 27 Apr 2023 11:14:08 GMT

I have sample data in Excel for few years .

year value

2020 100

2021 120

2022 150

Syntax:

((currentyear)/(previousyear))^(1-n))-1

Excel used the below formula

Excel formula : = ((F6/E6)^(1/1))-1

F6 - current year

e6 - previous year

n-number of years

n= Number of years . if we select 2020 and 2021 , N will be 1 year of aging.

How to convert this formula to power bi dax. looking for your support .

Re: Excel Formula to Dax

FreemanZ — Thu, 27 Apr 2023 11:58:29 GMT

hi Anonymous

seems you are calculating compound annual growth rate.

i tried the following, for your reference.

1) expand your dataset and transpose it to:

Year	Value
2017	40
2018	50
2019	80
2020	100
2021	120
2022	150

2) add a calculate table for the n parameter, like:

n = SELECTCOLUMNS( GENERATESERIES(1,5), "n", [Value] )

3) plot a slicer with n[n] column and a table visual with year column and a measure like:

CAGA% = VAR _currentyear = MAX(data[Year]) VAR _n = SELECTEDVALUE(n[n]) VAR _firstyear = _currentyear-_n VAR rate = DIVIDE( SUM(data[value]), CALCULATE( SUM(data[value]), data[Year]=_currentyear-_n ) )^(1/_n) -1 RETURN IF(rate<>-1, rate, "")

it worked like:

verified the calculation with Excel formula:

Re: Excel Formula to Dax

Anonymous — Thu, 27 Apr 2023 12:31:11 GMT

FreemanZ thank you so much ... its working

Re: Excel Formula to Dax

Anonymous — Thu, 27 Apr 2023 12:34:59 GMT

FreemanZ i am getting the below Error

Re: Excel Formula to Dax

Anonymous — Tue, 09 May 2023 06:15:18 GMT

FreemanZ How to get compound growth rate month wise ..

Re: Excel Formula to Dax

FreemanZ — Tue, 09 May 2023 06:37:16 GMT

hi Anonymous

the logic is the same, only changing the term from year to month, like:

CMGR = (Final Month Value / Initial Month Value) ^ (1 / # of Months) – 1

Re: Excel Formula to Dax

Anonymous — Wed, 17 May 2023 10:35:13 GMT

FreemanZ how to use here Date Column instead of Year .. year number format.when i change it to Date . value is blank

Var _currentyear=MAX(data[year])

VAR _currentyear = MAX(data[Year])

Re: Excel Formula to Dax

FreemanZ — Wed, 17 May 2023 10:46:01 GMT

could you post some sample data?

Re: Excel Formula to Dax

Anonymous — Wed, 17 May 2023 11:30:00 GMT

FreemanZ please find thje sample data . i have data range 2019 to 2025 ..

REGION DATE YEARS VALUE

EAST 01-01-2019 2019 454

WEST 01-02-2019 2019 954

EAST 01-03-2019 2019 354

NORTH 01-04-2019 2019 554

SOUTH 01-05-2019 2019 654

WEST 01-06-2019 2019 454

WEST 01-07-2019 2019 454

EAST 01-08-2019 2019 354

NORTH 01-09-2019 2019 554

WEST 01-10-2019 2019 654

EAST 01-11-2019 2019 454

WEST 01-12-2019 2019 954

EAST 01-01-2020 2020 454

WEST 01-02-2020 2020 954

EAST 01-03-2020 2020 354

NORTH 01-04-2020 2020 554

SOUTH 01-05-2020 2020 654

WEST 01-06-2020 2020 454

WEST 01-07-2020 2020 454

EAST 01-08-2020 2020 354

NORTH 01-09-2020 2020 554

WEST 01-10-2020 2020 654

EAST 01-11-2020 2020 454

WEST 01-12-2020 2020 954

Re: Excel Formula to Dax

Anonymous — Thu, 18 May 2023 05:00:57 GMT

FreemanZ how to modify above measure for quadrant comparision for year on year

Re: Excel Formula to Dax

FreemanZ — Sat, 20 May 2023 07:13:27 GMT

hi Anonymous

not sure about what do you mean by quadrant comparison, could you please elaborate that?