locked
Calculate and Aggregate in PowerPivot using Prior Period Data RRS feed

  • Question

  • I am trying to develop a calculation in PowerPivot that purely looks at the trend of specific measures over a period of time.  If the change from one month to the next is positive, return a value of 1.  If the change is negative return -1.  Let's call this the Direction measure.  I then need to sum the 1s & -1s to arrive at a relative trend value which I'll call the Trend measure.  This is more of an experimentation so the validity of the calculation has not been settled but that is beyond the point.

    The problem is that I cannot then SUM the Direction measure as part of the Trend measure because only columns are permitted in aggregate calculations.

    So I then tried to create the Direction calculation as a calculated column in the PowerPivot data.  I attempted to compare the current records data to the prior month's data.  However, using the following formula to retrieve the prior month's data of a given record returned no value.

    CALCULATE(SUM(Value),PREVIOUSMONTH(DateColumn))

    Any assitance/tips would be appreciated.


    Jeff T Jones
    Tuesday, August 23, 2011 5:53 PM

Answers

  • Jeff, to give you a formula I would need to take a look at your data. Do you have a row for each product/month, or you need to perform previous aggregations... the data format is important.

    Nevertheless, a quick hint is that while you cannot SUM a measure, you can SUMX it quite easily.

    Thus, SUMX ({the period you want to use}, [Trend]) will work fine.

     


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by Jeff Jones Wednesday, August 24, 2011 12:26 PM
    Tuesday, August 23, 2011 9:03 PM

All replies

  • Jeff, to give you a formula I would need to take a look at your data. Do you have a row for each product/month, or you need to perform previous aggregations... the data format is important.

    Nevertheless, a quick hint is that while you cannot SUM a measure, you can SUMX it quite easily.

    Thus, SUMX ({the period you want to use}, [Trend]) will work fine.

     


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by Jeff Jones Wednesday, August 24, 2011 12:26 PM
    Tuesday, August 23, 2011 9:03 PM
  • Alberto,

    SUMX worked perfectly thank you.  I knew I was missing something simple such as this.

    Regards,

    Jeff


    Jeff T Jones
    Wednesday, August 24, 2011 12:27 PM