How to Write CAGR formula in DAX

[vs_7]

Hi,

I am trying to figure out how to get the Compound Annual Growth Rate for some data but the measure not working.

I have Sales measures- Current year sales, first year sales and count of total years -1

The formula in Excel:

(B23/B2)^(1/C23)-1

b23- current year

b2 -first year

c23 - count of all years -1

measures :

FirstYearSales =

VAR FirstYear = MIN ( 'year_sort'[Year] )

RETURN

    CALCULATE (

        SUM ( 'Final data'[amt] ),

        'year_sort'[Year] = FirstYear

    )

CurrentYearSales =

VAR CurrentYear = MAX ( 'year_sort'[Year])

RETURN

    CALCULATE (

        SUM ( 'Final data'[Amount] ),

        'year_sort'[Year] = CurrentYear

    )

CountYears = COUNTROWS(Year_Sort)-1

CAGR =

    VAR CurrentYearSales = [CurrentYearSales]

    VAR FirstYearSales = [FirstYearSales]

    VAR CountYears = [CountYears]

RETURN

    POWER (

        DIVIDE ( CurrentYearSales,FirstYearSales),1 / CountYears) - 1

Any help is greatly appreciated.

Thanks!!

@amitchandak 

[vs_7]

Hi @amitchandak ,

I got the CAGR result for first year.

But how can i achieve the CAGR value for the all the remaining years?

[v-tangjie-msft]

Hi @vs_7 ,

Please try :

FirstYearSales =
VAR FirstYear = MINX (all('year_sort'), 'year_sort'[Year] )
RETURN
CALCULATE (
SUM ( 'Final data'[amt] ),
Filter(ALL('year_sort),'year_sort'[Year] = FirstYear)
)

CurrentYearSales =
VAR CurrentYear = MAX ( 'year_sort'[Year])
RETURN
    CALCULATE (
        SUM ( 'Final data'[Amount] ),
        Filter(ALL('year_sort'),'year_sort'[Year] = CurrentYear)
    )

CountYears = COUNTROWS(ALL(Year_Sort))-1
 

CAGR =
    VAR CurrentYearSales = [CurrentYearSales]
    VAR FirstYearSales = [FirstYearSales]
    VAR CountYears = [CountYears]
RETURN
    POWER (
        DIVIDE ( CurrentYearSales,FirstYearSales),1 / CountYears) - 1

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vs_7]

Hi @mwegener @amitchandak ,

please suggest

Issue not resolved from above dax

i'm getting values only for first year when uisng current year measure and current year sales measure not working

measures

Currentyear = CALCULATE(max(Year_Sort[Year]),ALL('Final data'[Amount]))-1

CAGR =

    VAR CurrentYearSales = [Currentyear]

    VAR FirstYearSales = [FirstYearSales]

    VAR CountYears = [CountYears]

RETURN

    POWER(

        DIVIDE(CurrentYearSales, FirstYearSales), 1 / CountYears) - 1

[vs_7]

Hi, @v-tangjie-msft @amitchandak @parry2k 

Issue not resolved from above dax

i'm getting values only for first year when uisng current year measure and current year sales measure not working

measures

Currentyear = CALCULATE(max(Year_Sort[Year]),ALL('Final data'[Amount]))-1

CAGR =

    VAR CurrentYearSales = [Currentyear]

    VAR FirstYearSales = [FirstYearSales]

    VAR CountYears = [CountYears]

RETURN

    POWER(

        DIVIDE(CurrentYearSales, FirstYearSales), 1 / CountYears) - 1

[v-tangjie-msft]

Hi @vs_7 ,

Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

[vs_7]

Hi @v-tangjie-msft @amitchandak @mwegener@@parry2k 

Issue not resolved from above dax

i'm getting values but data is not showing properly
when im selecting start year and end year in slicer its showing same in both column

out put in table visual
year   Startyear ENd year
2020  4796.24 4796.24
2022  6663.67 6663.67

Required Output
year  Startyear  ENd year
2020  4796.24
2022                 6663.67

Measures

END_Year_Sales =

VAR CurrentYear = MAX ( Revenue[YEAR1])

RETURN

    CALCULATE (

        SUM ( Revenue[Amt] ),

        Revenue[YEAR1] = CurrentYear

    )

Start_YearSales =

VAR FirstYear = MIN ( Revenue[YEAR1] )

RETURN

    CALCULATE (

        SUM ( Revenue[Amt] ),

        Revenue[YEAR1] = FirstYear

    )

[amitchandak]

@vs_7 , I have made a few changes in the measure.

measures :
FirstYearSales =
VAR FirstYear = MINX (allselected('year_sort'), 'year_sort'[Year] )
RETURN
CALCULATE (
SUM ( 'Final data'[amt] ),
'year_sort'[Year] = FirstYear
)

CurrentYearSales =
VAR CurrentYear = MAXX (allselected('year_sort'), 'year_sort'[Year])
RETURN
CALCULATE (
SUM ( 'Final data'[Amount] ),
'year_sort'[Year] = CurrentYear
)

CountYears = COUNTROWS(Year_Sort)-1

or
CountYears = MAXX (allselected('year_sort'), 'year_sort'[Year]) - MINX (allselected('year_sort'), 'year_sort'[Year] )

CAGR =
VAR CurrentYearSales = [CurrentYearSales]
VAR FirstYearSales = [FirstYearSales]
VAR CountYears = [CountYears]
RETURN
POWER (
DIVIDE ( CurrentYearSales,FirstYearSales),1 / CountYears) - 1