I've been beating my head against this for the past week and I'm getting closer to my solution. I am trying to find out a monthly total and spread it across that many months based on:
Implementation Date - Which has an alliance to my master calendar date
$(eTotalSavings) - Which is SUM(FY2000+FY2001+...FY2020)
CalcMonth - Which is PICK(MATCH(CalendarMonthName, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5) as CalcMonth
MonthNameandYear - Which is MonthName(TempDate) as CalendarMonthAndYear,
I created a variable for monthly total which is $(eTotalSavings/CalcMonth)
Now I have my monthly total in a pivot table table, which displays the following, but I want the monthly savings to spread across Implementation Date Start month + CalcMonth. So Jul should spread across 12 months while August 1 month and September 2 months ect how can this be accomplished with Set Analysis?
| Date | CalendarMonthAndYear | Jul 2010 | Aug 2010 | Sep 2010 | Oct 2010 | Nov 2010 | Dec 2010 | Jan 2011 | Feb 2011 | Mar 2011 | Apr 2011 | May 2011 | Jun 2011 |
| 7/1/2010 | | 2220983.61 | - | - | - | - | - | - | - | - | - | - | - |
| 8/1/2010 | | - | 7423377.38 | - | - | - | - | - | - | - | - | - | - |
| 9/1/2010 | | - | - | 1034323.97 | - | - | - | - | - | - | - | - | - |
| 10/1/2010 | | - | - | - | 2582659.997 | - | - | - | - | - | - | - | - |
| 11/1/2010 | | - | - | - | - | 630290.82 | - | - | - | - | - | - | - |
| 12/1/2010 | | - | - | - | - | - | 631737.664 | - | - | - | - | - | - |
| 1/1/2011 | | - | - | - | - | - | - | 1481003.16 | - | - | - | - | - |
| 1/11/2011 | | - | - | - | - | - | - | 33351.47333 | - | - | - | - | - |
| 2/1/2011 | | - | - | - | - | - | - | - | 811157.6257 | - | - | - | - |
| 3/1/2011 | | - | - | - | - | - | - | - | - | 718391.0425 | - | - | - |
| 3/31/2011 | | - | - | - | - | - | - | - | - | 4828.12125 | - | - | - |
| 4/1/2011 | | - | - | - | - | - | - | - | - | - | 1235129.877 | - | - |
| 5/1/2011 | | - | - | - | - | - | - | - | - | - | - | 298935.66 | - |
| 6/1/2011 | | - | - | - | - | - | - | - | - | - | - | - | 1335653.623 |
