Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |