SSAS RelativeCalendar CurrentYear CurrentMonth

• bkaurelien

  SSC Veteran

  Points: 213

  More actions

  July 13, 2016 at 3:00 am

  #147660

  I have a dimension DimDate (Year, month, days, quater) and in this dimension I would like to create a relative calendar corresponding to the relative year and relative month. Eg in: - Relative Year I would like to have a Member : current year, current year-1 - Relative Month i would like to have a member : Current month, current month -1, current month-2...

  how to do it in SSAS?

  I add named calculations for those Relative Year, Month in my dimension DimDate

  

  After i create calculate members like this :

  

  [Annee en cours] => StrToMember ("[Date].[Calendar].[Calendar Year].&[" + Format(Now(),"yyyy") + "]" )

  [Annee en cours -1] => StrToMember ("[Date].[Calendar].[Calendar Year].&[" + Format(Now(),"yyyy") + "]" ).Lag(1)

  ....

  i have this error when i use the calculate members RelativeYear in my cube to have order quantity for CurrentYear, currentYear-1,...

  ERROR => " A setcan notcontaincalculated membershas been detected"

  

  Please do you no what is round!?

  Thanks you

  Aurelien

• PB_BI

  SSCoach

  Points: 17463

  More actions

  July 13, 2016 at 6:14 am

  #1889825

  It sounds as if you need to implement time intelligence in your cube. The best (if not the simplest) way of doing this is by using the DateTool dimension methodology, detailed here: http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx

  To get rid of your actual error, you'd need to put your members into a SCOPE, but I think you have an opportunity to build something that is far more scalable using the above.

  ---

  I'm on LinkedIn

• bkaurelien

  SSC Veteran

  Points: 213

  More actions

  July 13, 2016 at 8:06 am

  #1889861

  I PB_BI

  thanks for you reply.

  but i don't want to use time intelligence.

  when you say i can use scope it is like this

  scope([Annee en cours]);

  this = StrToMember ("[Date].[Calendar].[Calendar Year].&[" + Format(Now(),"yyyy") + "]" )

  end scope;

  Thanks

• PB_BI

  SSCoach

  Points: 17463

  More actions

  July 14, 2016 at 1:55 am

  #1890013

  bkaurelien (7/13/2016)

  ---

  I PB_BI

  thanks for you reply.

  but i don't want to use time intelligence.

  when you say i can use scope it is like this

  scope([Annee en cours]);

  this = StrToMember ("[Date].[Calendar].[Calendar Year].&[" + Format(Now(),"yyyy") + "]" )

  end scope;

  Thanks

  Apologies, I wasn't clear. SCOPE is only required if you need time intelligence. The problem with your MDX is that you are creating a set and then using StrToMember. You need to use StrToSet instead.

  ---

  I'm on LinkedIn