Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

CAGR for multiple periods

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi Anonymous,

 

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi Anonymous,

     

    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