Calculating measure for the current month of Previous Year

  • Hello,

    I would like to calculate measures in MDX for the current month of the previous year.

    Example : Today, we are the 20/03/2014.

    My measure Nb Open Days n-1 will be the aggregation of my measure Nb Open Days still the 20/03/2013.

    I have build my calculation like this :

    SUM(

    Generate

    ({Existing [Time].[Date].Children},

    {PARALLELPERIOD([Time].[Year - Month - Date].[Year],

    1,

    [Time].[Year - Month - Date].CURRENTMEMBER)

    }

    ),[Measures].[Nb Jour Ouvré])

    Through the browser, when I put my different attributes in the body of my query, it works.

    But for the time attribute "Day_Week" which contains the day of the week (monday, tuesday...), it doesn't work.

    The browser displays only "(null)" value.

    I don't understand why ? Someone can help me ?

  • Hi,

    Today I faced a similar situation and I started using the ParallelPeriod function, but later I found this blog entry:

    Retrieve selected year data and same level data from previous year

    I think it would be very helpful.

    Kind Regards,

    Paul Hernández
  • Hello,

    Thank you for your link and your help.

    But, I have found this link that corresponds more to my issue :

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2f822a5-2a7a-4ec5-9db4-35a107d8fcb9/mdx-parallelperiod-month-to-month

    Here is my MDX request if it can help :

    SUM(Generate(Existing {[Time].[Date].Children},

    {PARALLELPERIOD([Time].[Year - Month - Date].[Year]

    ,1

    ,OPENINGPERIOD([Time].[Year - Month - Date].[Date],[Time].[Year - Month - Date].CurrentMember))

    :

    PARALLELPERIOD([Time].[Year - Month - Date].[Year]

    ,1

    ,TAIL(DESCENDANTS([Time].[Year - Month - Date].CurrentMember,[Time].[Year - Month - Date].[Date],LEAVES)

    ,1).Item(0))

    })

    ,[Measures].[Nb Jour Ouvré]

    )

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

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