Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Hi @ramachr,
In your scenario, Beginning Value 3yr will always return blank value because the section FILTER('Table','Table'
Beginning Value 3yr = CALCULATE(SUM('Table'[AUM]),FILTER('Table','Table'[Year]= MAX('Table'[Year])-3))
Best Regards,
Qiuyun Yu
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
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'
If issue persists, please share some sample data and expected results about the Beginning Value 3yr for our analysis.
Best Regards,
Qiuyun Yu
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |