Excel Formula to Dax

[Anonymous]

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 .

[Anonymous]

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

[FreemanZ]

hi @Anonymous 

not sure about what do you mean by quadrant comparison, could you please elaborate that?

[Anonymous]

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

[FreemanZ]

could you post some sample data?

[Anonymous]

@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

[Anonymous]

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

[FreemanZ]

hi @Anonymous 

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

[Anonymous]

@FreemanZ  i am getting the below Error 

[Anonymous]

@FreemanZ  thank you so much ... its working 

[FreemanZ · Accepted solution]

hi @Anonymous 

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: