This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I need to be able to compare actuals data with the forecast data for the same day based on the forecast 3 months prior to the actual date. Eg: The February actuals need to be compared to the February forecast as determined in november. Any thoughts on how this could be done dynamically based on the actuals period?
As always, I appreciate your collective help and wisdom.
Depending on your data I can think of a couple different ways to get your data lined up...
If in November you actually had a specific date in February forecasted (i.e., on November 20 you forecasted what the actual would be for February 19), then you could take that forecast with the forecast date, split it out into a data flow, and then join it back with the forecasted date joined to the actual date. Then it is an easy formula to compare the amounts.
If it is more of a "relative date" situation, where you want to see what the forecast was 90 days ago, you could use a multi-row formula tool with the "Num Rows" set to 90 and use the expression "Row-90:ForecastValue" created in a new field. (Of course, if you have records for only business days, you would want to adjust that to Row-60 or something like that.) Then you again have an easy formula to compare the amounts.
The attached workflow will generate a forecasts based on a 12 month period (up through 3 months ago), and compare the results to actuals from last month; also for a 365 period through 90 days ago, then forecast for 90 days with comparison.
The numbers passed in are just randomly generated, but the basic process should also work on real data. Hope it helps!