In the example attached, I am trying to create a calculated field that shows a simple average sales YOY growth.
My challenge is that I need to have a filter that ignores months that do not have any sales reported by both this year and last year columns. (data set attached)
So in the example:
- Calculated field is calculating Jan-Dec 2012 growth from all Jan-Dec 2011 (based on TY/LY rows).
- Table calculation is calculating Jan-Dec 2012 growth from Oct-Dec 2011 (because the filter ignores Jan-Sep 2011 data).
What I need to have is a field that shows Jan-Dec 2012 growth from Oct-Dec 2011, but at the same time, for 2011, shows Oct-Dec 2011 growth from Oct-Dec 2010 (here based on the rows LY data). So in the example, a row shows 60% (2011) and 4% (2012).
I am thinking some sort of calculation that always brings months of previous year that are filtered (in this case Oct-Dec), and always apply those month to only LY data/calculated field.
Any suggestions to this would be very much appreciated!