I have a question on best practice with regards to version and switchover date...
My model uses 3 separate data sources for Salary, Income and Other Costs, which are stored in 3 separate modules.
Initially, I built the model assuming that each of my 3 data sources will be actualised to the same time point.
At the start of the budget process, I would upload data covering Jan- June for all three data sources and set switchover date on the forecast version to July.
I now want to actualise my data to August, but actuals are only available for Income and Other Costs. Therefore if I actualise Income and Other Costs to August and set switchover to September, my forecast modules work correctly for Income and Other Costs. However, my *Salary* forecast modules now show actuals up to June, blanks during July and August and forecast from September.
As a workaround, I have created a separate version with July switchover inorder to store the salary data but this requires hardcoded version selection when integrating + sparsity issues.
Is there a best practice for dealing with this scenario?
I often use the Current Period flag in Time Settings to help flag which periods are Actual. This can be done in a Time Settings modules using START()<CURRENTPERIODSTART()
However, in your case where the actual periods may differ, I would set up an Time Lookup module (with no dimensions) and create a line item for each source, formatted as Month. Depending on your logic, you may be able to make these formula driven (if, for example, Salaries were always a month behind Income)
Then create a module by month (e.g. Time Settings by Month), with the three boolean line items
e.g. Income Actuals Periods? with the formula START()<=START(Time Lookups.Income Actual Month)
You can then refer to these in the line item formulas
IF Time Settings by Month.Income Actual Periods? THEN Actual.Data ELSE Forecast.Data
If needed, you could also use Dynamic Cell Access to control input for Forecasts. See this app for more details if you need to
I would ask, can you combine the three sources into one module? If you use a list to hold the sources, you can save having three line items, and use the list in the above example instead
If not, you could always create a line item subset to combine the data sources and then use the line item subset as a dimension in the modules above
See here for more details on Line Item Subsets
I hope that helps
Thanks for your detailed response. From your advice, I was able to solve the problem by using a "Time Settings by Month" module with Boolean line items that defined which data source to use for various periods.
Many thanks for your help on this,