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