Calculate CAGR% for Moving Annual Total Dynamic formula

[enghone007]

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. 

[enghone09]

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. 

[v-easonf-msft]

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.

[enghone09]

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

[v-easonf-msft]

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