I'm trying to figure out an easier way to calculate a trend within a PowerPivot table. I read that you can't use the trend function in a PowerPivot measure so I ended up doing the math in a measure. It's a real pain to do, but I read that you could do it with the OLAP PivotTable Extensions add-in. But I can't figure out how to get it to work.
Table
CUSTOMER_ID YEAR
107 2010
106 2011
105 2011
104 2012
107 2011
106 2012
106 2010
...
What is desired is in red:
Pivot Table
2010 2011 2012 2014 (Projection)
# Customers 100 125 175 208.33
If I make a new measure using the OLAP PivotTable extension with hardcoded values: TREND({100,125,175},{1,2,3},4) I get an error. I can't figure out the syntax for hard coded numbers let alone with actual measures. Any help would be much appreciated.
Table
CUSTOMER_ID YEAR
107 2010
106 2011
105 2011
104 2012
107 2011
106 2012
106 2010
...
What is desired is in red:
Pivot Table
2010 2011 2012 2014 (Projection)
# Customers 100 125 175 208.33
If I make a new measure using the OLAP PivotTable extension with hardcoded values: TREND({100,125,175},{1,2,3},4) I get an error. I can't figure out the syntax for hard coded numbers let alone with actual measures. Any help would be much appreciated.