MDX - Cube Calculated member sliding 12 month comparison with previous month data

  • Hi,

    I want to compare Aggregate of following two different resultsets:

    SELECT

    [Measures].[Internet Sales Amount] ON COLUMNS,

    NON Empty

    {

    LastPeriods(

    12, [Date].[Calendar].[Month].[January 2008]

    )

    }

    On Rows

    FROM

    [Adventure Works];

    GO

    SELECT

    [Measures].[Internet Sales Amount] ON COLUMNS,

    NON Empty

    {

    LastPeriods(

    12, [Date].[Calendar].[Month].[February 2008]

    )

    }

    On Rows

    FROM

    [Adventure Works];

    GO

    Now actually i want to add a calculated member in the cube in a way that in Current Month, it compares aggregated sales of previous 12 months with the aggregate of previous 12 month from one month before previous month.

    For example

    In march 2008

    Aggregate(Mar2007 .... Feb2008) ,

    Aggregate(Feb2007 .... Jan2008) ,

    Aggregate(Mar2007 .... Feb2008) - Aggregate(Feb2007 .... Jan2008) AS Rolling_12Month_Growth.

    Note: it is different from Month-Over-Month Growth.

    So any clue please?

    Thanks.

  • Hi Moderator,

    This is my second MDX question in "Analysis Services" forum of SSC and NO answer at all.

    Am i on the right place for such questions? or what else?

    Thanks.

  • The best help i found on this question is available on following Link. Thanks to Pavel Pawlowski. Excellent work.

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1d00b9fd-80f9-493c-8d04-b125882a6e5c/#1d00b9fd-80f9-493c-8d04-b125882a6e5c

    Thanks and Cheers.

  • This is the right place, but we don't have a lot of MDX posters, so there might not be anyone that can easily answer.

  • I think that the following will give you what you are after

    WITH MEMBER Measures.CurrentMonth AS 'SUM ( [Date].[Calendar].Currentmember.lag(11) : [Date].[Calendar].Currentmember, [Measures].[Internet Sales Amount])'

    MEMBER Measures.PriorMonth AS 'SUM ( [Date].[Calendar].Currentmember.lag(12) : [Date].[Calendar].Currentmember.lag(1), [Measures].[Internet Sales Amount])'

    Member Measures.Difference AS 'Measures.CurrentMonth - Measures.PriorMonth'

    SELECT

    {measures.currentmonth, measures.priormonth, Measures.Difference} ON COLUMNS

    FROM

    [Adventure Works]

    where ([Date].[Calendar].[Month].[February 2004])

Viewing 5 posts - 1 through 4 (of 4 total)

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