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?