Create Monthly Average For Multiple Years Filtered to Current Year's Time Period
I'm trying to figure out a way to compare the average monthly amount of a value field across years for only the months that have elapsed in the current year. In other words, if I only have January through September data for 2014, I want to compare the average monthly amount for 2014 to the January through September period of prior years.
I also want to be able to calculate the average monthly amount for the full year in the prior years to just the elapsed period of the current year. So again, in my example, the prior years would be the total of the value field divided by 12 but the current year would only be divided by 9.
The table would look like this (with the 2014 Jan-Dec number either blank or showing the same value as in Jan-Sep).