cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

## 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 .

1 ACCEPTED SOLUTION
Super User

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 10
Post Partisan

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

Super User

Post Partisan

@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])`

Super User

could you post some sample data?

Post Partisan

@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

Post Partisan

@FreemanZ  How to get  compound growth rate month wise ..

Super User

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

Post Partisan

@FreemanZ  i am getting the below Error

Post Partisan

@FreemanZ  thank you so much ... its working

Super User

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:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors