MDX - Cumulative Sum Not working when selecting multiple dates

0
votes

I've build an MDX calculated member that returns the Cumulative Sales over time. - the query returns correct results if no filters applied or the user filters with 1 month

my problem is that when the user selects multiple months the query returns the cumulative for the whole year

here is what i wrote using Dynamic Set, Calculated Member and Scope:

Dynamic Set:

PeriodToDate = EXISTING PeriodsToDate([Date].[Calendar - YQMD].[Year],[Date].[Calendar - YQMD].CurrentMember)

Calculated Member:

( PeriodsToDate([Date].[Calendar - YQMD].[Year],[Date].[Calendar - YQMD].CurrentMember) ,[Measures].[Sales Amount - Foreign] )

Scope:

Scope ( [Date].[Calendar - YQMD].[Year], [Measures].[Sales Amount - Foreign] )

This = Aggregate([PeriodToDate],[Measures].[Sales Amount - Foreign])

End Scope

1
mdxcumulative-sum

1 Answers

2
votes

Try this:

Create Dynamic Set CURRENTCUBE.SelectedMonths as [Date].[Calendar - YQMD].[Month].Members;

Scope ( [Date].[Calendar - YQMD].[Year], [Measures].[Sales Amount - Foreign] );
    This = Aggregate(PeriodsToDate([Date].[Calendar - YQMD].[Year], Tail(Existing SelectedMonths,1).Item(0).Item(0)));
End Scope;