Skip to main content
cancel
Showing results for 
Search instead 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

Reply
THENNA_41
Post Partisan
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
FreemanZ
Super User
Super User

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:

YearValue
201740
201850
201980
2020100
2021120
2022150

 

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:

FreemanZ_0-1682596450604.png

FreemanZ_3-1682596656568.png

 

verified the calculation with Excel formula:

FreemanZ_2-1682596601334.png

 

 

View solution in original post

10 REPLIES 10
THENNA_41
Post Partisan
Post Partisan

@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?

THENNA_41
Post Partisan
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])

 

 

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

THENNA_41
Post Partisan
Post Partisan

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

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

THENNA_41
Post Partisan
Post Partisan

@FreemanZ  i am getting the below Error Screenshot 2023-04-27 180428.png

THENNA_41
Post Partisan
Post Partisan

@FreemanZ  thank you so much ... its working 

FreemanZ
Super User
Super User

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:

YearValue
201740
201850
201980
2020100
2021120
2022150

 

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:

FreemanZ_0-1682596450604.png

FreemanZ_3-1682596656568.png

 

verified the calculation with Excel formula:

FreemanZ_2-1682596601334.png

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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