topic Re: Calculate CAGR% for Moving Annual Total Dynamic formula in DAX Commands and Tips

Calculate CAGR% for Moving Annual Total Dynamic formula

enghone007 — Fri, 27 May 2022 10:56:37 GMT

Hi everyone,

I have been trying to find an answer for the CAGR calculation that I need but I wasn't able to find a solution. So I am hoping someone can help me. I used the following video to build by Dynamic CAGR% model but it is not working for what I need.

https://www.youtube.com/watch?v=IUdsztn62aA

Please let me explain

I want to calculate CAGR for moving 12 months. For example, say the latest data period I have is till April 2022, I want to calculate CAGR using starting year April 2019 or April 2018. How do I make the start year and month to be dynamic based on the end year and month? And then the next following month in May, I want to use May 2022 MAT period.

Do I need a MAT year column in my date table? If so, how do I write M query for that?

Any solution is welcome. Appreciate your help. Thank you.

Re: Calculate CAGR% for Moving Annual Total Dynamic formula

v-easonf-msft — Mon, 30 May 2022 10:43:21 GMT

Hi, enghone007

Please try the following M query to offset your original field ‘Year’ based on the current month.:

=Date.Year(Date.AddMonths([Date], -(Date.Month(DateTime.LocalNow()))))

Then you can replace your original filed 'Year' with new field 'Period' in your model.

Meaure:

Ending Period = CALCULATE(MAX('Revenue'[Dynamic Period] ),ALL('Revenue'[Dynamic Period]))

Ending Period Value = CALCULATE([Total Revenue],FILTER(ALL(Revenue[Dynamic Period]),Revenue[Dynamic Period]=Revenue[Ending Period]))

No.of Periods -1 = [Ending Period]-VALUES('Period Table'[Dynamic Period])

Starting Period Value = CALCULATE('Period Table'[Total Revenue],FILTER(ALL(Revenue[Dynamic Period]),Revenue[Dynamic Period]= VALUES('Period Table'[Dynamic Period]) ))

CAGR = ([Ending Period Value]/[Starting Period Value])^(1/[No.of Periods -1])-1

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

Re: Calculate CAGR% for Moving Annual Total Dynamic formula

enghone09 — Wed, 01 Jun 2022 11:16:21 GMT

Thank you for your help. but Mquery doesn't give me the period that I need. So for example

May-2021

Jun-2021

Jul-2021

Aug-2021

...

April 2022

The whole twelve months I want the column to show "MAT April 2022". And then when May 2022 data is available, i want the data to go 12 months back.

Re: Calculate CAGR% for Moving Annual Total Dynamic formula

v-easonf-msft — Tue, 07 Jun 2022 06:31:03 GMT

Hi, enghone09 enghone007

Please change M codes as below:
Period Year:

=Text.From(Date.Year(Date.AddMonths([Date], 1 + (Date.Month(DateTime.LocalNow())))))

Dynamic Period:

=Text.Combine( { "MAT", Text.From(Date.Year(Date.AddMonths([Date], 1 + (Date.Month(DateTime.LocalNow()))))), Date.MonthName(Date.AddMonths(DateTime.LocalNow(), - 1)) }, " " )

(MAT May 2022)

Then replace the fields in my original Dax formulas with these new fields.

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate CAGR% for Moving Annual Total Dynamic formula

enghone09 — Wed, 30 Aug 2023 00:59:35 GMT

Thank you so much. It definitely help solved the problem.