How to use 2 different Measures on 2 different Time Dimensions?

• Question

• 

  

  0

  Sign in to vote

  What I am trying to do it get the total Premium for Calendar MTD based on a measure I created (comm_prem - new_bus).   I also need Premium for Calendar PY MTD - but I dont' want to use the same measure, I need to just use comm_prem.
  Then when I have these 2 amounts, I would want a Percent Growtch.
  
  I figured this might have to be done via MDX.  
  
  
  This is what I get:
                                           Existing Bus                                        Comm_premium
                                           Calendar MTD      Calendar PY MTD      Calendar MTD            Calendar PY MTD
  Sales Rep 1    Oct 2009         I WANT THIS       Don't want this         Don't want this          I WANT THIS
  
  Since the Hirearchy is selecing Calendar MTD and Caleandar PY MTD, which I need both, but only want to display Calandar MTD for Existing_Bus and Calendar PY MTD for Comm_Prem 
  
  
  This is how the MDX looks via ProClarity. Note the Member Existing Business is the one that needs to be calculated differntly.
  

  SELECT

   

  NON EMPTY { { { [<##<MEMBER!svoth!Existing Business>##>], [Measures].[Commissionable Premium] } * { [Written Date].[Date Calculations].[Calendar MTD], [Written Date].[Date Calculations].[Calendar PY MTD] } } } ON COLUMNS ,

   

  NON EMPTY { { { [Agency].[Sales Rep].[All Agencies].CHILDREN } * { [Written Date].[Calendar].[Month].&[200910] } } } ON ROWS 

   

  FROM [Sales]

  CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
  
  Any help?

  Tuesday, February 2, 2010 9:15 PM