cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors