Pages

Thursday, October 19, 2017

SSAS + Forecast calculations MDX

Following forecast member returns forecast figures for a given measure based on the entire history.

WITH MEMBER 
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
AS LinRegPoint(
    Rank(
        [Period].[Extended Year - Quarter - Month].CurrentMember,
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members
        ),
    Exists(
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members,
        [Period].[Calendar Year - Quarter - Month].[Month].Members),

    ([Measures].CurrentMember, [Measures Utility].[Measure Calculations].DefaultMember),
    Rank(
    [Period].[Extended Year - Quarter - Month].CurrentMember,
    [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members)
)

SELECT

([Measures].[RXA Value] *
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]

) on 0,
NON EMPTY {[Period].[Extended Month].Children} 

ON 1
FROM [Cube Name]


Forecasts based on 3 months data points
CREATE MEMBER CURRENTCUBE.[Measures Utility].[Measure Calculations].[Linear Regression Forecast - 3 Data Points]
AS LinRegPoint(
    Rank([Period].[Operational Year - Quarter - Month].CurrentMember,
            {
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Lag(2):
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Parent.Parent.LastChild.LastChild
            } --[Output Period]
         ),  -- Measures.OutputX (Current Financial Year)
         Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3), -- [Input Period] -- 3 Months
    (    [Measures].CurrentMember,
        [Measures Utility].[Measure Calculations].DefaultMember), -- Measures.Input Y
    Rank(
        [Period].[Operational Year - Quarter - Month].CurrentMember,
        Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3) --[Input Period] -- 3 Months
        ) -- Measures.InputX
    ),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;