Forum Discussion

Revati265's avatar
Revati265
Regular Visitor
3 years ago

Calculate CAGR

I'm trying to calculate CAGR for Customers using quarters of the year.

 

Objective:
Calculate CAGR for customers using every quarter of the year starting from 2017-4 to 2022-4

 

My Calculations:
Beginning Value = CALCULATE(SUM('Data'[Customer_Name]),FILTER('Data','Data'[Year Qtr]=2017-4))
Ending Value = CALCULATE(SUM('Data'[Customer_Name]),FILTER('Data','Data'[Year Qtr]=2022-4))

CAGR = ([Ending Value]/[Beginning Value])^(1/21)-1


End result need a separate column for CAGR in the dataset.

 

Can someone please advice what I need to do.

Thanks in advance!

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Revati265 , if need this qtr vs last qtr , then you can measures for that like these examples using date table

     

    example

     

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))


    Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

    Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
    Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

     

    You can also consider Offset

    Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U