Forum Discussion
Excel Formula to Dax
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 .
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:
10 Replies
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:
FreemanZ thank you so much ... its working
FreemanZ i am getting the below Error
FreemanZ How to get compound growth rate month wise ..
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
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])
could you post some sample data?
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
FreemanZ how to modify above measure for quadrant comparision for year on year
hi Anonymous
not sure about what do you mean by quadrant comparison, could you please elaborate that?