I need to plot the monthly cost values depending on what year the user selects and show the Yearly Avg for all the previous years. So if the user selects 2014, I need the chart to show 2012 Avg Cost (61.5) as the first bar, 2013 Avg Cost (40.75) as the second bar and then each monthly value for 2014
The data is as follows
I now need to add two additional lines to the bar chart,
1) Goal Line: If I select 2015, then I need a goal line for all months in 2015 (straight line) at 67*0.9 =60.3 which is a 10% reduction from the previous year average
2) Year to Date Average for all values in 2015, so for Jan it would be 35, for Feb 26, March 24.7, April 34.3 etc.
Hahahaha, thanks! But as a note, I found out that the YTD Average line only works for year 2015 and 2014. before that it gives some weird results. If are going to be looking for values past 2013 (or last two years in your case) we will need to further tweak it.
Let me know what you think.