0

Situation: I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation. (so I can use measure security on calculations as explained here)

SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

I also made a time calculations which I scope for the whole measure group including the calculation above. (as explained here)

I create empty members for the calculations:

    --YTD Calculations
    CREATE MEMBER CURRENTCUBE.[Calender Calculations].[YTD-1]                       AS NULL;     
    CREATE MEMBER CURRENTCUBE.[Calender Calculations].[YTD]                     AS NULL;  
-- MOVING ANNUAL TOTAL Calculations
CREATE MEMBER CURRENTCUBE.[Calender Calculations].[MAT-1]                       AS NULL;  
   CREATE MEMBER CURRENTCUBE.[Calender Calculations].[MAT]                     AS NULL;
--SCOPE MEASUREGROUPMEASURES 

I scope the measuregroup on which the calculations will be performed:
SCOPE (MeasureGroupMeasures("Sales") 
        );

Next I scope the time calculations for the different Time Hierarchys:
SCOPE ([Calender].[Jaar].[Jaar].members,[Calender].[Calender ID].members); 

       --YTD          
        ([Calender Calculations].[YTD]=
        Aggregate(
        CrossJoin({[Calender Calculations].[Current Period]},
        PeriodsToDate(
        [Calender].[Month Hierarchy].[Jaar],
        [Calender].[Month Hierarchy].CurrentMember))
        )
        ); 

       --YTD -1    
        ([Calender Calculations].[YTD-1]=
        Aggregate(
        Crossjoin({[Calender Calculations].[Current Period]},
        PeriodsToDate(
        [Calender].[Month Hierarchy].[Jaar],
        ParallelPeriod(
        [Calender].[Month Hierarchy].[Jaar],1,
        [Calender].[Month Hierarchy].CurrentMember))
        )
        ));
    --MAT       
        ([Calender Calculations].[MAT]=
        Aggregate(
        CrossJoin({[Calender Calculations].&[Current Period]},
        ParallelPeriod([Calender].[Month Hierarchy].[Month],11,[Calender].[Month Hierarchy].CurrentMember) :
        [Calender].[Month Hierarchy].CurrentMember      
        ))); 

    --MAT-1      
        ([Calender Calculations].[MAT-1]=
        Aggregate(
        CrossJoin({[Calender Calculations].&[Current Period]},
        ParallelPeriod([Calender].[Month Hierarchy].[Month],23,[Calender]. [Month Hierarchy].CurrentMember) :
        ParallelPeriod([Calender].[Month Hierarchy].[Month],12,[Calender].[Month Hierarchy].CurrentMember)      
        ))); 

  --SCOPE Calendar END
    END SCOPE;   

Close the measuregroup Scope

--SCOPE MEASUREGROUPMEASURES END
END SCOPE;

Results: When I query the cube by using a ‘base measures’ and the ‘calculated measure’ the results for the time calculations are correct for the base measure but incorrect for the ‘calculated measure’ C because the measure first gets calculated and after that the time calculation is being done, resulting in aggregating the results.

Example: The current month is April 2015 Every month has a score of 5%. The YTD measure gives 20% (5% for jan, feb etc.) Which has to be 5%

My question: How can I change the time calculation or the ‘calculated measure’ so I get the right results?

JAL
  • 41,701
  • 23
  • 172
  • 300
  • At first glance this looks like a solve order issue. Take a look at this article by Chris Webb, which might help you track down your issue. http://blog.crossjoin.co.uk/2014/10/14/mdx-solve-order-scope_isolation-and-the-aggregate-function/ – The Dumb Radish Sep 17 '15 at 15:28
  • Thanks! It was as simple as that. In this case, moving the block with all the calculations of type C to the end of the script resulted in correct solving order in the cube in all cases. – P Meindertsma Sep 21 '15 at 09:39
  • No problem. Glad I could help. – The Dumb Radish Sep 21 '15 at 12:29

0 Answers0