Creating a Dynamic Forecast Layout

Use Calculation Input Controls to create a forecast layout that can be updated dynamically from a central setting.

To update all your similar forecast layouts at the same time, you can create dynamic input controls by adding attributes to the Version dimension of your model. For example, you could add a current version flag and a cut-over date.

Each month (or designated time interval), someone must manually update attributes in the Version dimension of your model, and edit the attribute values that are being used in the calculation input control formulas.

After updating the model, the following changes are visible in the dynamic layout:

The cut-over date is shifted by one interval.
A new forecast version becomes the current forecast.

Verify the Version Dimension Columns in the Model

The Version dimension of your model can include attributes that allow you to control and update all forecast layouts. The following types of columns can be added to your model's Version dimension for working with cut-over dates:

A Current column – identifies which version to use as the current version.
A CutOverDate column – the cut-over date value.

Note

NOTE: For fiscal years, use calendar values in the CutOverDate column. For example, if your fiscal year begins in July, the value should be 201807 (YYYYMM).

Formula: Using the FIND Function

The FIND () function looks for a member that matches the value in the first parameter and returns its attribute n the second parameter. The third parameter contains the ID value associated with the value in the second parameter.

FIND (Value to match, Property to match, Property to map)

Value to match – the flag for your current version, or another Input Control.
Property to match – the specific column from your Version dimension, for example,[d/Version].[p/CURRENT].
Property to map – the ID associated with the property that you matched, for example, [d/Version].[p/ID]).

Procedure: Create the Current Calculation Input Control

To create the first Calculation Input Control (Current), do the following:

Under Rows or Columns in the Builder tab, select Add Measures/Dimensions and add the Cross Calculations dimension.
In the Cross Calculations dimension, select Add Calculation.
From the Calculation Editor Type, select Forecast.
From the Properties section, under Version, select Create a New Calculation Input Control.
Provide a name for the input control, for example, Current.
In the Formula section, select Click to Add Formula.
Provide a name for the formula.
In the Edit Formula area, type the formula.

Remember
You can press Ctrl + Space in the formula area to display a list of suggestions, or type [ for a list of valid measures and dimensions.

Example
FIND('X', [d/Version].[p/CURRENT], [d/Version].[p/ID])
Click OK; click OK again.

The Calculation Input Control is created.
To close the Calculation Editor, click OK.

Procedure: Create the Cut-Over Date Calculation Input Control

To create the second Calculation Input Control (Cut-Over Date), do the following:

In the Builder, in the Cross Calculations dimension, select the current calculation and then select (Edit Calculation).
From the Properties cut over date section, select Specific Date and then select Click to Select Values Create a New Calculation Input Control.
Provide a name for the input control, for example, Cut-Over Date.
In the Formula section, select Click to Add Formula.
Provide a name for the formula.
In the Edit Formula area, type the formula.

Remember
You can press Ctrl + Space in the formula area to display a list of suggestions, or type [ for a list of valid measures and dimensions.

Example
FIND([@Current], [d/Version].[p/ID], [d/Version].[p/CutOverDate])
Click OK; click OK again.

The Calculation Input Control is created.
To close the Calculation Editor, click OK.

The results of the calculations are displayed in your table and are updated when you change your cut-over date.

How the Calculations Work

At story load time, the “Cut-Over Date” Calculation Input Control formula will be processed as follows:

The system first processes the Calculation Input Control called “Current”.
The system then searches in the “ID” column of the Version dimension for the value returned from the “Current” calculation.
When the value is found, the system then uses the value from that row in the column “CutOverDate”.

This calculation returns a string that is then translated into a time period. The system understands and interprets time in the following formats:

YYYY: A year in four-digit representation
YYYYQ: A quarter, Q, in one-digit representation {1,2,3,4}
YYYYMM: A month, MM, in two-digit representation {01,02,03,04,05,06,07,08,09,10,11,12}
YYYYMMDD: A day

These are the values that you can use in attributes in the Version dimension when you want the system to interpret these strings as time values.

Since the date dimension has multiple hierarchies, the system also sets the corresponding date hierarchy as it is being used in the Calculation Input Control.

Example: Version Dimension Attributes and Dynamic Forecast Results

You can use any value for the “Current” attribute, but the “CutOverDate” attribute should be a valid time period.

Example

Version Dimension of the Model
Description	Category	Current	CutOverDate
Actual	Actuals
Copy of Forecast	Forecast
Estimated	Forecast
FC17Q1	Forecast		20171
FC17Q2	Forecast		20172
FC17Q3	Forecast	LE1	20173
FC17Q4	Forecast	LE2	20174
FC2020	Forecast
Forecast	Forecast
NewFC	Forecast

The following example shows the formulas that use the Version dimension attributes.

Example

Current and Cut-Over Date Formulas
Current Formula Name	Current Formula	Cut-Over Date Formula
LE1	`FIND('LE1', [d/Version].[p/Current] ,[d/Version].[p/ID] )`	`FIND([@LE1], [d/Version].[p/ID], [d/Version].[p/CutOverDate] )`
LE2	`FIND('LE2', [d/Version].[p/Current] ,[d/Version].[p/ID] )`	`FIND([@LE2], [d/Version].[p/ID], [d/Version].[p/CutOverDate] )`

The following table shows the results of applying the Current and Cut-Over Date Calculation Input Controls. Notice how the cut-over date (black bar) changes from LE1-20172 to LE2-20173.