In my company, we have new monthly forecasts for the year every month. That is, in Feb, we will have what we called '1+11', so the values shown is 1 month of actual sales, and 11 month of forecast. Now, that 11 month of forecast will change when March comes. That is, for Marh we will have '2+10', and we will have 2 months of actual sales, and 10 months of Forecast. The key is that the forecast for Mar to Dec for 2+10, is not the same as the forecast values for Mar to Dec for 1+11.
I would like to display the Year end amount base on the different forecast every month. That is, when we are in Feb, I want to display the total of 1+11, and when we are in Mar, I want to display the total of 1+11 and 2+10 (and possibily do more detailed analysis on the different sets of forecasts). So the old forecast of the different months needs to be there, even when that old data is refreshed to the new one at the end of the month.
So How can I load the data, or structure the data base such that it can do that? Keep in mind, that I will have many other graphs that will display the current forecast amounts on monthly basis and etc. Please let me know if I am no clear on this, and your help is appreciated.
There are many different ways to approach this problem. One way that I'd recommend is to create an extra table, that we often call "As Of Dates" - you create a separate field for Dates (or Months) that you want to show on the screen, and you'd associate this date with your other date (actual/forecast), and while associating, you will set certain flags for those Months that need to be related to the "As Of Month'.
For example, for "As OF Month" = Mar-11, all the Months between Apr-11 and Feb-12 need to be listed with the flag "Include Forecast" = 1, and all other months should be listed with the same flag = 1.
So, in this example, your table will include the following:
As Of Month Month Fcst_Flag
Mar-11 Jan-11 0
Mar-11 Feb-11 0
Mar-11 Mar-11 0
Mar-11 Apr-11 1
Mar-11 May-11 1
etc...
Now, if you use "As of Month" as your chart dimension, then you can limit your Forecast by the following formula:
