LinRegPoint Expression ( MDX Query ) for forecasting......

  • I have a factsummary table that looks like the below:-

    I am using DimDate Dimension from the demo analysis services project.

    I have populated the dimension table accordingly. i.e uptil 2018.

    I need to look into the actuals(TOTAL) for 5 years and project the numbers for the next 3 fiscal years.

    I am converting an excel report into SSRS using SSAS as a data source.

    I have already defined a cube, set the time dimensions. In excel, i would use the linear expresison Y=a.x + b

    In SSAS world, there is an equivalent, but i need help with getting the right results.

    I suspect that the calculation is not considering the range of the fiscal years.....

    DateKey FiscalYear total scenario_Key

    ----------- ----------- ----------- -----------------

    20100101 2010 19700 1

    20110101 2011 19800 1

    20120101 2012 19900 1

    20130101 2013 19650 1

    20140101 2014 19451 1

    20150101 2015 NULL 2

    20160101 2016 NULL 2

    20170101 2017 NULL 2

    --(8 row(s) affected)

    WITH

    MEMBER Measures.[Internet Sales Forecast] AS

    LinRegPoint(

    Rank( [Date].[Calendar].CurrentMember, [Date].[Calendar].CurrentMember.LEVEL.MEMBERS),

    Descendants( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember.LEVEL),

    [Measures].[Total],

    Rank( [Date].[Calendar], [Date].[Calendar].CurrentMember.LEVEL.MEMBERS))

    ,FORMAT_STRING="#,0"

    SELECT

    DESCENDANTS( [Date].[Calendar].[Calendar Year],

    [Date].[Calendar].[Year]

    ) ON COLUMNS,

    {

    [Measures].[Total],

    [Measures].[Internet Sales Forecast]

    } ON ROWS

    FROM [Adventure Works]


    Kindest Regards,

    Kunal Gandre
    >
    Snr. SQL DBA

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply