Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 .
Solved! Go to Solution.
hi @THENNA_41
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 how to modify above measure for quadrant comparision for year on year
hi @THENNA_41
not sure about what do you mean by quadrant comparison, could you please elaborate that?
@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
hi @THENNA_41
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
hi @THENNA_41
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:
User | Count |
---|---|
24 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |