Create 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]).
Note

Formulas using the FIND function can only be used in forecast layouts or forecast calculations.

Procedure: Create the Current Calculation Input Control

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

  1. Under Rows or Columns in the Builder tab, select Add Measures/Dimensions and add the Cross Calculations dimension.

  2. In the Cross Calculations dimension, select Start of the navigation path Next navigation step Add CalculationEnd of the navigation path.

  3. From the Start of the navigation pathCalculation Editor Next navigation step TypeEnd of the navigation path, select Forecast.

  4. From the Properties section, under Version, select Create a New Calculation Input Control.

  5. Provide a name for the input control, for example, Current.

  6. In the Formula section, select Click to Add Formula.

  7. Provide a name for the formula.

  8. 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])

  9. Click OK; click OK again.

    The Calculation Input Control is created.

  10. 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:

  1. In the Builder, in the Cross Calculations dimension, select the current calculation and then select (Edit Calculation).

  2. From the Start of the navigation pathProperties Next navigation step cut over dateEnd of the navigation path section, select Specific Date and then select Start of the navigation pathClick to Select Values Next navigation step Create a New Calculation Input ControlEnd of the navigation path.

  3. Provide a name for the input control, for example, Cut-Over Date.

  4. In the Formula section, select Click to Add Formula.

  5. Provide a name for the formula.

  6. 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])

  7. Click OK; click OK again.

    The Calculation Input Control is created.

  8. 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:
  1. The system first processes the Calculation Input Control called “Current”.
  2. The system then searches in the “ID” column of the Version dimension for the value returned from the “Current” calculation.
  3. 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.

Note

A returned string that is not in YYYYMMDD format will first be converted to the first date based on its own date granularity. For example, 2023 will be converted to 20230101 while 202302 will be converted to 20230201.

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.