Calculate CAGR

[Revati265]

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!

[amitchandak]

@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