Configure Rolling Forecast

Author: Kepion Product Team

Introduction

To configure a Rolling Forecast, we’ll need to design a Form that can be updated from a single location using Application Variables. Below is an example Form that's been configured using a Rolling Forecast. It's scoped from three months prior of Actuals data to twelve months of Forecast data, with the totals aggregated at the end. Both of these scopes can be adjusted as needed.

Before You Begin

To key to constructing this Form is configuring the column axis correctly. The column axis includes both the Time Dimension and Scenario Dimension. In addition, we'll define some Standard Variables to specify the Forecast scope. The scope is dynamically based on the current month, which means we'll need to define a Standard Variable to represent that, first.

1. Create Current Month Variable

Below, we've created a variable called: Current Month. This variable represents the Time MemberId for the value of the current month.

This value can either be manually updated, or we can use a SQL script to automatically update this value based on the actual current date. The following is an example of a script which will run every time the application is deployed.

UPDATE [dbo].[ApplicationVariables]
   SET [VariableValue] =  (SELECT CONVERT(varchar(12) , GETDATE(), 112 )) / 100 * 100
WHERE VariableName = N'Current Month'

2. Rolling Prior and Forward Months Variables

Next, we can define two more Standard Variables to display the Forecasting time-frame. We'll need two different variables here: Rolling Prior Months and Rolling Forward Months.

Rolling Prior Months - Prior 3 months:

[Time].[Months].&[${Current Month}].Lag(3):[Time].[Months].&[${Current Month}].Lag(1)

Rolling Forward Months - Current month and the following 11 months:

[Time].[Months].&[${Current Month}]:[Time].[Months].&[${Current Month}].Lag(-11)

3. Scenario Dimension Calculation

For the two aggregation columns in our Rolling Forecast Form, we'll need to create two Scenario Dimension Members to represent the Current FY Actual & Forecast summation and the Rolling Year Forecast.

4. MDX Calculation Definition

Now that we have the necessary Dimension Members, we need to define the calculations for them in an MDX Rule. Let’s go ahead and create a new MDX Rule in the MODELER called Dynamic. This Rule should be added to the same Model that we'll be creating our Form in.

/*-----------------------
    Scenario Aggregation
*/-----------------------
 
//Current FY Actual & Forecast:
//Part 1: Using Actual values for months ranged between January and the month before current
SCOPE
(
    [Scenario].[Scenario].[Current FY Actual & Forecast]
    ,{[Time].[Fiscal Calendar].&[${Current Month}].Parent.Parent.FirstChild.FirstChild
      :[Time].[Fiscal Calendar].&[${Current Month}].Lag(1)}
);
THIS = [Scenario].[Scenario].[Actual];
END SCOPE;
 
//Part 2: Using Foreast values for months ranged between current month and December
SCOPE
(
    [Scenario].[Scenario].[Current FY Actual & Forecast]
    ,{[Time].[Fiscal Calendar].&[${Current Month}]
      :[Time].[Fiscal Calendar].&[${Current Month}].Parent.Parent.LastChild.LastChild}
);
THIS = [Scenario].[Scenario].[Forecast];
END SCOPE;
 
//Rolling Year Forecast: Using Foreast values for current months and next 11 months
SCOPE
(
    [Scenario].[Scenario].[Rolling Year Forecast]
    ,{[Time].[Months].&[${Current Month}]
      :[Time].[Months].&[${Current Month}].Lag(-12)}
);
THIS = [Scenario].[Scenario].[Forecast];
END SCOPE;

5. Form Design

Now let's create a new Form, or update an existing Form, to configure our Rolling Forecast. In the example below, we've created a simple Form with just the Account, Scenario, and Time Dimensions.

In the LAYOUT tab we should use the Months Hierarchy, instead of Fiscal Calendar, for the column as the scope of the Form is not restricted to a single Fiscal Year. Make sure to add Scenario as a Filter Variable, as well.

Then we need to define the column axis. We can combine the time variables from earlier in the Time Dimension with corresponding scenarios in the Scenario Dimension to achieve the correct Form structure, as shown below.

Now let’s preview the Form, and it should show the months and scenarios correctly.