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,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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'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