I have this requirement. We have three years of sales data for 12 months. the user wants to forecast the next two years based on the average of the last three years. For eg, for 2013 Jan, it will average of 2010 jan, 2011 jan and 2012 Jan. This itself is possible . The issue is that for 2014, we have to use 2011,2012 and forecast of 2013 which we are calculating. Not sure how to do both in same chart. let me know if anybody has good ideas.
Also in pivot table, I am calculating the average Sales for 2013 jan as =avg(aggr(sum(sales),fam,Month). We have to sum this all to get the subtotal. This expression is giving average. It would be helpful if somebody helped on that end too. Thanks.