Post reply

MDX rolling total only for selected periods

  • January 11, 2017 at 7:17 am

    #325182

    Hi All

    I have received a requirement to build a rolling total. I have found many articles articulating how you can sum for a period using periodstodate and so forth.

    My requirements however is to do the rolling total only on selected period members.

    Below is an example of the periods and what is required

    Now for the measure. When hardcoding the "first" member the calculation works as advertised.

    CREATE MEMBER CURRENTCUBE.[Measures].[Rolling Total]

    AS SUM( ([Date].[Calendar].&[2015].&[12]:[Date].[Calendar].Currentmember)

    , [Measures].[Sales Amount])

    ,

    VISIBLE = 1 ;

    This however needs to change so that the "first" period member / Current member is automatically calculated.

    Was thinking in the lines of:

    CREATE MEMBER CURRENTCUBE.[Measures].[Rolling Total 2]

    AS SUM( ((

    strtomember(HEAD(

    [Date].[Calendar].Currentmember.member_unique_name

    ,1))

    ):[Date].[Calendar].Currentmember), [Measures].[Sales Amount])

    ,

    VISIBLE = 1 ;

    Please any suggestions are most welcome.

    Thanks

    Jacques

  • January 18, 2017 at 10:38 am

    #1923484

    SQL Adventurar - Wednesday, January 11, 2017 7:17 AM

    Hi AllI have received a requirement to build a rolling total. I have found many articles articulating how you can sum for a period using periodstodate and so forth.My requirements however is to do the rolling total only on selected period members.Below is an example of the periods and what is requiredNow for the measure. When hardcoding the "first" member the calculation works as advertised.CREATE MEMBER CURRENTCUBE.[Measures].[Rolling Total] AS SUM( ([Date].[Calendar].&[2015].&[12]:[Date].[Calendar].Currentmember), [Measures].[Sales Amount]), VISIBLE = 1 ; This however needs to change so that the "first" period member / Current member is automatically calculated.Was thinking in the lines of:CREATE MEMBER CURRENTCUBE.[Measures].[Rolling Total 2] AS SUM( ((strtomember(HEAD([Date].[Calendar].Currentmember.member_unique_name ,1))):[Date].[Calendar].Currentmember), [Measures].[Sales Amount]), VISIBLE = 1 ;Please any suggestions are most welcome.ThanksJacques

    I was not able to test this comprehensively, but use your first code snippet and try to replace the 
    [Date].[Calendar].&[2015].&[12]

    with

    OpeningPeriod([Date].[Calendar].[Month],[Date].[Calendar].currentmember).lag(1)

    The above of course assumes that your month level in the hierarchy is labelled "Month".

Viewing 2 posts - 1 through 1 (of 1 total)

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