locked
GEOMEAN filtered on Date range RRS feed

  • Question

  • 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

     

    Tuesday, February 15, 2011 4:30 PM

Answers

  • I belive I have a working prototype the filters to a date range!  Finally figured out a way to filter the log10 expression to the date range I was interested in. This version seems to do the trick for my scenario:

     

    =power(10,(Calculate(Sumx('RETURNS',LOG10([totalret])),
    DATESBETWEEN(StmtPeriod[PeriodEndDate],
    DATE(1985,3,31),
    LASTDATE(StmtPeriod[PeriodEndDate])))
    /Calculate(Count(RETURNS[totalret]),
    DATESBETWEEN(StmtPeriod[PeriodEndDate],
    DATE(1985,3,31),
    LASTDATE(StmtPeriod[PeriodEndDate])))))^4-1


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com
    • Marked as answer by Chad - TSP Tuesday, February 15, 2011 8:06 PM
    Tuesday, February 15, 2011 8:06 PM