The financial department has a huge excel document where the sales linear projections are calculated for a period of 6,12 and 18 month through excel formulas. However, when we need to add product, company, area, scenarios to the equation, the complexity sky rocks. One would need to have a single excel sheet for any combination that would be studied.
The development of formulas that will enable the sales measures forecast calculation for the following months given any dimension combination that can be produced from the model.
After some investigation and some reverse engineering from the existing models, the implementation strategy resides on a linear regression formula creation that will enable the calculation of the sales evolution throughout the following months. The linear regression formulas have three components:
The selected measure
The periods that will be used as the source for the projection calculation
A measure that specifies the period order (so that we can infer the line evolution)
The final goal of the linear regression formula is to find a function that represents, in the closest possible way, the values of the measure in the specified periods, producing a projection of the line (which can have a positive or a negative slope) as you can see in the picture below.
Using this formula, the 0 mark will represent the first period used as the source for the projection and the b value represents the value of the measure in that period. This way, if we want to calculate any future mark in our line, its value will always be Y= mx+b, where m is the line slope, x is the period we are trying to calculate and b is the value of the first period.
Application: The following steps consist on the MDX concretization of the logic explained before:
Creation of a time set that contains the periods that will be used in the linear projection calculation;
Definition of a calculated measure returning the value of the slope in the regression line, y = mx + b. (using the LINREGSLOPE function);
Definition of a calculated measure with the initial value. (using the LINREGINTERCEPT function);
Final measure definition that calculates the projection in a specified period that range's x months from the initial projection period.
Time set definition CREATE DYNAMIC SET CURRENTCUBE.[LINREGSET] AS ([Date].[Fiscal].DefaultMember.Lag(6) : [Date].[Fiscal].DefaultMember.PrevMember); Given that the default member for the Date Fiscal hierarchy is the current month, this set will return the previous 6 months from the current month. This enables the projection to always be automated and based in the last 6 months.
Definition of a calculated measure returning the value of the slope in the regression line, y = mx + b CREATE MEMBER CURRENTCUBE.[Measures].[Linear Projection – Slope – Sales] AS LINREGSLOPE( LINREGSET, [Sales], RANK ([Date].[Fiscal].CurrentMember, [LINREGSET] --The records will be sorted ) , VISIBLE = 0; This measure calculates the Sales variance on a monthly basis and it's based on LINREGSET set periods created before.
Definition of a calculated measure with the initial projection. CREATE MEMBER CURRENTCUBE.[Measures].[Linear Projection – Intercept – Sales] AS LINREGINTERCEPT( LINREGSET, [Sales], RANK ([Date].[Fiscal].CurrentMember, [LINREGSET] --The records will be sorted ), VISIBLE = 0; This measure calculates the initial period value for the LINREGSET set created before.
CREATE MEMBER CURRENTCUBE.[Measures].[Sales Projection]
— Initial Value
[Linear Projection – Intercept – Sales] +
— Slope * Nº of Periods after the initial one
[Linear Projection – Slope – Sales] *
Count( ([Date].[Fiscal].DefaultMember.lag(6) : [Date].[Fiscal].CurrentMember) )
Definition of the final measure that calculates the projection in a specified period that range's x months from the initial period.
As the final result we will obtain a Forecast measure that can be used with any combination of the cube dimensions without having any extra mdx additional calculations.
The image below illustrates the use of the company dimension to obtain two forecasts, for two distinct companies, based on the Sales Projection Measure.