cancel
Showing results for
Did you mean:
New Member

## Calculate CAGR% for Moving Annual Total Dynamic formula

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.

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.

3 REPLIES 3
Frequent Visitor

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.

Community Support

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()))))),
},
" "
)``````

(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.

Community Support

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