Awhile back I got some help creating a GEOMEAN calc in DAX on PowerPivot. This function example works on the entire table:
=power(10,(Sumx('Returns',LOG10([ReturnPlus1]))/COUNTX('Returns',[ReturnPlus1])))^4-1
I want to enhance this calc as my customer would like a number of variations on the shape of the results that the geomean would
be calculated on. I'm struggling to refactor the above formula to restrict it to particuluar time range back in time and use the current time member as the start time. For example. I want to pivot out 2 years worth of quarters on the
columns axis and have a GEOMEAN measure for each quarter that goes back an inception date. I'm struggling to find a solution that both respects the attributes on the row,column and filter axis and allow for calcuting returns going back in time to particular end
date. If this doesn't make sense I can share some samples.
So far I have worked out the calc as follows (This does not work right):
=power(10,(Sumx('RETURNS',LOG10([totalret]))
/Calculate(Count(RETURNS[totalret]),
DATESBETWEEN(StmtPeriod[PeriodEndDate],
DATE(1985,3,31),
LASTDATE(StmtPeriod[PeriodEndDate])))))^4-1
I'm pretty sure the LOG10 portion is wrong as it needs to be filtered in the same way as the denominator portion of the expression
but I can't seem to find a way to use the LOG10 function with a filter or calculate statement. I think I might be close or maybe not...
Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
