SSAS calculation with division combined with a time calculation

385 Views Asked by At

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?

0

There are 0 best solutions below