Home Forums Data Warehousing Analysis Services MDX - Cube Calculated member sliding 12 month comparison with previous month data RE: MDX - Cube Calculated member sliding 12 month comparison with previous month data

  • August 8, 2012 at 10:42 pm

    #1523558

    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])