Calculating measure for the current month of Previous Year

• yhafraoui

  SSC Enthusiast

  Points: 191

  More actions

  March 24, 2014 at 5:00 am

  #150084

  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 ?

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  March 25, 2014 at 8:16 am

  #1699898

  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

• yhafraoui

  SSC Enthusiast

  Points: 191

  More actions

  March 25, 2014 at 8:42 am

  #1699917

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

  )