CAGR for multiple periods

[ramachr]

I need to calculate CAGR over 3 and 5 year periods.  In the example here

https://powerbi.tips/2016/05/measures-calculate-cagr/

CAGR  is calculate over the entire range in the table.  My data has 10+ years of history.  So I tired this

Ending Value = CALCULATE(SUM('Table'[AUM]),FILTER(''Table',''Table'[Year]=MAX(''Table'[Year])))

Beginning Date 3yr = date(year(max('Table'[Year]))-3,12,31)

Beginning Value 3yr = CALCULATE(SUM('Table'[AUM]),FILTER('Table','Table'[Year]= [Beginning Date 3yr]))

Ending Value is correct (and filters properly).  Beginning Date 3yr is the correct date.   However, Beginning Value 3yr returns blank.  How do I get the value 3years from the last date?

TIA,

R.

[ramachr · Accepted solution]

Hi Quiyun,

So if I create a measure

Beginning Date Measure = date(year(max('Table'[Year]))-3,12,31)

then

BeginValueMeasure = CALCULATE(sum('Table'[AUM]),filter('Table','Table'[Year]='Table'[Beginning Date Measure]))

Returns a blank. 

However, if I create a column

Beg Date Col = date(year(max('Table'[Year]))-3,12,31)

then

Begin Value = CALCULATE(sum('Table'[AUM]),filter('Table','Table'[Year]='Table'[Beg Date Col]))

returns the correct value.

New to DAX, so I'm not sure why the measure didn't work.  I'm interested in finding out the reasoning for intellectual reasons, but the work issue has been resolved.  Thanks for your help.

Raghu

[v-qiuyu-msft]

Hi @ramachr,

In your scenario, Beginning Value 3yr will always return blank value because the section FILTER('Table','Table'[Year]= [Beginning Date 3yr], Beginning Date 3yr return the date value while 'Table'[Year] is numuric value, which isn't equal. Please change the measure like below:

Beginning Value 3yr = CALCULATE(SUM('Table'[AUM]),FILTER('Table','Table'[Year]= MAX('Table'[Year])-3))

Best Regards,
Qiuyun Yu

[ramachr]

Hi Qiuyun,

I still get a blank value.  If I just look at Max('Table'[Year]) - 3, the value is 12/28/2016.  I only have year end values, so it makes sense not getting back a value.

Just to be clear, the Year column in Table is all dates - formatted as dates.  So I also get that subtracting 3 from Year gets me three days before year end.

Similiraly, if I calculate Max('Table'[Year]) - 366, I get the correct number for 12/31/2015.  But I don't really want to hard code the 366.

Thanks,
Raghu

[v-qiuyu-msft]

Hi @ramachr,

Assume the Beginning Date 3yr = date(year(max('Table'[Year]))-3,12,31) returns the date 12/31/2013, does the table contains a record about 12/31/2013? If not, then the measure Beginning Value 3yr = CALCULATE(SUM('Table'[AUM]),FILTER('Table','Table'[Year]= [Beginning Date 3yr])) will return blank because the condition 'Table'[Year]= [Beginning Date 3yr] doesn't meet.

If issue persists, please share some sample data and expected results about the Beginning Value 3yr for our analysis.  

Best Regards,
Qiuyun Yu

[ramachr · Accepted solution]

Hi Quiyun,

So if I create a measure

Beginning Date Measure = date(year(max('Table'[Year]))-3,12,31)

then

BeginValueMeasure = CALCULATE(sum('Table'[AUM]),filter('Table','Table'[Year]='Table'[Beginning Date Measure]))

Returns a blank. 

However, if I create a column

Beg Date Col = date(year(max('Table'[Year]))-3,12,31)

then

Begin Value = CALCULATE(sum('Table'[AUM]),filter('Table','Table'[Year]='Table'[Beg Date Col]))

returns the correct value.

New to DAX, so I'm not sure why the measure didn't work.  I'm interested in finding out the reasoning for intellectual reasons, but the work issue has been resolved.  Thanks for your help.

Raghu

[v-qiuyu-msft · Accepted solution]

Hi @ramachr,

Glad to hear that the issue has been solved.

In my opinion, the calculated column Beg Date Col will return values based on each year value in the table. While the Beginning Date Measure will return date based on the visual context to calculate Max(Year) then return the value. This should be the difference. For more information, you can learn measures and calculated columns below:

Tutorial: Create your own measures in Power BI Desktop

Tutorial: Create calculated columns in Power BI Desktop

Best Regards,
Qiuyun Yu