Hi did anyone figure this out yet?
I was able to calculate the moving sum and the results of that is what I have pasted on the table above.
I need to calculate one column which is the average column ( in red) that gives me :
e.g. sum (Jan - Dec moving sum of sales) / number of months
Appreciate your help!
That's the format yes, but the numbers are different from I am expecting.
I calculated the avg by sum (moving sum for each year) divided by number of months.
Note: 12 doesn't have data for Jan-Nov since there's not enough data to calculate the 12 month moving sum for those months.
Year Avg 2012 569,651 2013 1,077,138 2014 1,082,432 2015 1,099,078 2016 1,054,929
Please help to figure out how to get the above average numbers for each year.
Was anyone able to figure out how to calculate the average column mentioned above?
Wow thanks John!
However I am unable to figure out how you got that. When I try to re-create the calculation that you have "if last()= 0 then [Window Average of Moving Sum] end" i get different answers.
Apologies for the back and forth, appreciate your time and help on this.
This is perfect. Thank you for helping out. I learned a lot of new things in the process as well ( Nested Table Calculations!)