CAGR for a 3yr period

[JamilESpencer]

Hello Everyone,

I am tryign to calculate CAGR just for a 3 year period. I have seen 2 post on here but the solution doesn't seem to work. Objective:

• Calculate CAGR for a 3 year period (2014 - 2016) by Product
• If a product doesn't have a sale in 2014 then the CAGR is zeroed out. 

My calculations:

3 yr Period = DATE(YEAR(MAX('Date'[Year]))-3,12,31)

Beginning Value = CALCULATE(SUM(Sheet1[LINE AMOUNT FC]),FILTER('Date','Date'[Year]=Sheet1[3 yr Period]))

Ending Value = CALCULATE(SUM(Sheet1[LINE AMOUNT FC]),FILTER('Date','Date'[Year]=MAX('Date'[Year])))

CAGR = IFERROR(([Ending Value]/[Beginning Value])^(1/[# of Years])-1,0)

1. End result CAGR is zeroing out for all products which I know is incorrect when cross-referenced in excel
2. The card visual for the '3 yr period' is illustartign the following date: 1902-12-31

Can someone advise if the '3 yr period' caluclation is correct to pull just the last 3 years? Note, I also have visual filters to only see data for 2016 - 2014.

Thanks in advance!

[_Kevin_]

If like me you were looking for a Dax CAGR calculation and didn't find it then here is my answer after figuring it out:

In excel the formula would be = ((150/100)^(1/3) ) - 1 where:

•    100 is the base year and 150 is the later forecast year value
•    ^ = to the power of, and
•     3 in the (1/3) is the number of years for which you are calculating the difference. So CAGR for a 2 year period would be to the power of ½ or 0.5.

So from Excel, there are 2 Divides and a Power.

First, make measures for the different years of sales, so for example [2020Sales] and [2023Sales]

Dax does Divide as: DIVIDE(<numerator>, <denominator> [,<alternateresult>])

Or in our case:

DIVIDE([2023Sales], [2020Sales] , 0 ) and

      DIVIDE(1, 3, 0)               

Dax does Power as POWER(<number>, <power>) 

So for us:

      POWER(<First Divide>, <Second Divide>)

To avoid zeros for all blank rows add in an If statement:

      IF(  ISBLANK([2020Sales]), BLANK(), <Restofformula>)

Also add a x 100 at the end if your formatting is not Percentage.

Then your Dax measure is:

CAGR20-23 = IF(  ISBLANK([2020Sales]), BLANK(), ((POWER(DIVIDE([2023Sales],[2020Sales],0),(DIVIDE(1,3,0)))-1)*100))

I had a problem in my custom visual at first that prevented it showing properly, that was related to the formatting and too many categories. Hence the x100 and I had to remove a category. If in doubt, put your new measure in the Pivot visualisation. If it still doesn’t show, check your parenthesis.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[v-jiascu-msft]

Hi @JamilESpencer,

Try these formulas please. If they don't work, please provide a sample pbix file. The results of DAX formulas depend on the context.

Beginning Value =
VAR maxYear =
    MAX ( 'date'[Year] )
RETURN
    CALCULATE (
        SUM ( Sheet1[LINE AMOUNT FC] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Year] = maxYear - 2 )
    )

Ending Value =
CALCULATE (
    SUM ( Sheet1[LINE AMOUNT FC] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) )
)

Best Regards!

Dale

[JamilESpencer]

Hi @v-jiascu-msft and @Ashish_Mathur

Sorry for my late response. I changed a table/column that I was pointing in my formulas  and got data to populate, however the percentages do not match what I have in excel. See my updated formulas below:

Test = DATE(YEAR(MAX(Sheet1[DOCDATE]))-3,12,31)

Beginning Value = CALCULATE(SUM(Sheet1[LINE AMOUNT FC]),FILTER(Sheet1,Sheet1[DOCDATE]=Sheet1[Test].[Date]))

Ending Value = CALCULATE(SUM(Sheet1[LINE AMOUNT FC]),FILTER(Sheet1,Sheet1[DOCDATE]=MAX(Sheet1[DOCDATE])))

# of Years = 2

CAGR = IFERROR((([Ending Value]/[Beginning Value])^(1/[# of Years]))-1,0)

I have created the following data set to replicate what I am trying to achieve, however, when I use my formulas I am still having an issue:

Brand	Product	Size	Date	Sales
Brand A	Fresh	4lb	20-Apr-14	$92,000
Brand A	Fresh	4lb	17-Sep-14	$25,000
Brand A	Fresh	4lb	05-Mar-14	$40,000
Brand A	Fresh	4lb	09-Mar-16	$15,000
Brand A	Fresh	8lb	09-Feb-14	$1,000
Brand A	Fresh	8lb	05-May-14	$10,000
Brand A	Fresh	8lb	04-Apr-16	$1,000
Brand A	Fresh	8lb	12-Dec-16	$5,000
Brand A	Fresh	16lb	30-May-16	$10,000

[Ashish_Mathur]

Hi,

You have shared the base data but not your expected result.  Share that as well.

[JamilESpencer · Accepted solution]

HI @Ashish_Mathur

I made this way more complicated than required. I achieved the end result in my real data by just referencing the sales year I needed to sum for both the beginning & end values.

Beginning Value = CALCULATE(SUM(Sheet1[LINE AMOUNT FC]),FILTER('Date','Date'[Year]=2014))

Ending Value = CALCULATE(SUM(Sheet1[LINE AMOUNT FC]),FILTER('Date','Date'[Year]=2016))

CAGR = IFERROR((([Ending Value]/[Beginning Value])^(1/2))-1,0)

I crossed referenced in excel and the result was the same. Thank you for your help nonetheless