I have a date dimension that has multiple hierarchies (as most do). I have a number of measures that are calculated as a running total, as shown in the example below.
AGGREGATE([Date].[Calendar].[Date].Members.Item(0):ClosingPeriod([Date].[Calendar].[Date], [Date].[Calendar].CurrentMember), [Measures].[Number Of Contracts])
My problem is that the running total only works for the Calendar hierarchy (which breaks everything down by year, month, and then day). If I instead use the Weeks hiearchy (which breaks things down by individual week), the calculation doesn't work, it just shows the same number for every week.
Is there a performant way in MDX to make the running total work for multiple hierarchies? Everything I've attempted so far has been quite slow.
One possible solution is below. By using the EXISTING keyword, one can get a list of all the Date members that exist in the current context, and then use those dates to define a range over which to aggregate the data. Based on my initial testing, the performance is similar to the calculation in the question.
AGGREGATE(NULL:TAIL(EXISTING [Date].[Date].[Date].Members).Item(0), [Measures].[Number Of Contracts])
Another, possibly more performant, solution is described at http://www.purplefrogsystems.com/blog/2010/08/mdx-calculated-member-spanning-multiple-date-dimensions/. It solves the problem by calculating ranges for all applicable hierarchies and then crossjoining those hierarchies together.
AGGREGATE({ NULL:[Date].[Calendar].CurrentMember } * { NULL:[Date].[Week].CurrentMember })
The only drawback of this solution is that it can become verbose if you have many hierarchies that do not have attribute relationships between eachother.