My SSAS cube uses currency conversion intelligence which will show some measures calculated from currency rates. However, in Date hierarchy dimension, I need to add more MDX to make my own Date hierarchy to show correct summation as follows.
> // Scope for sum [Calendar year - month - date].[Month] SCOPE
> ([Date].[Calendar year - month - date].[Month].MEMBERS,
> {[Measures].[Revenue], Measures.[Sales]});
> THIS = Sum( EXISTING [Date].[Calendar year - month - date].CurrentMember.Children ,[Measures].CurrentMember); END SCOPE;
>
> // Scope for sum [Calendar year - month - date].[Calendar year] SCOPE
> ([Date].[ Calendar year - month - date].[Calendar year].MEMBERS,
> {[Measures].[Revenue], Measures.[Sales]});
> THIS = Sum( EXISTING [Date].[Calendar year - month - date].CurrentMember.Children ,[Measures].CurrentMember); END SCOPE;
This works just fine on Month and Calendar Year level. However, when looking at the grand total in Excel, it always shows sum of all years. When I change the filter to select only 2 years, it still shows sum all years in grand totals.
