Last Year using Scope or Calculated Member - Rollup issue or Grand Total issue

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  July 4, 2014 at 12:52 am

  #146891

  Hi Folks,

  I'm getting stuck with the calculation of the last year using MDX.

  First I create a calculated member and the scope it like this:

  CREATE MEMBER CURRENTCUBE.[Measures].[Sales Discount LY] AS NULL;

  SCOPE([Measures].[Sales Discount LY], [Time].[Date].Members);

  this = (Cousin

  (

  [Time].[Year - Quarter - Month - Date].CurrentMember

  ,Ancestor

  (

  [Time].[Year - Quarter - Month - Date].CurrentMember

  ,[Time].[Year - Quarter - Month - Date].[Year]

  ).Lag(1)

  ),[Measures].[Sales Discount]);

  -- Overwrite for weeks

  SCOPE([Measures].[Sales Discount LY], [Time].[Year - Week - Date].[Week].Members);

  this = (Cousin

  (

  [Time].[Year - Week - Date].CurrentMember

  ,Ancestor

  (

  [Time].[Year - Week - Date].CurrentMember

  ,[Time].[Year - Week - Date].[Year]

  ).Lag(1)

  ),[Measures].[Sales Discount]);

  END SCOPE;

  END SCOPE;

  That works fine except that the grand total is not shown. Therefore I followed some suggestions and created a dummy named column and a real measure, then I overwrote the measure using the scope above and commented out the calculated member. Now I have the grand total, but the problem is some aggragations are not correct:

  

  just to add more confusion, it only happens with one year.

  What could be wrong? Any comment would be appreciated

  Kind Regards,

  Paul Hernández

• PB_BI

  SSCoach

  Points: 17461

  More actions

  July 4, 2014 at 8:49 am

  #1727103

  I can't see the picture....

  But why aren't you using PARALLELPERIOD instead of your cousin>ancestor.lag(1) thing above? It's simpler....

  [Code="Other"]

  SUM(

  PARALLELPERIOD

  ([Time].[Year - Quarter - Month - Date].[Year],

  1 ,

  [Time].[Year - Quarter - Month - Date].CurrentMember),

  [Measures].[Sales Discount])

  [/Code]

  🙂

  ---

  I'm on LinkedIn