SSAS MDX - PeriodsToDate not working when user selects multiple dates

Question

I've build a MDX calc member that give me the year-to-date (YTD) for budget, however if the user selects multiple months, it returns the total for year. Which I believe is CurrentMember function, but how do it I get the max from the set?

Aggregate(
    PeriodsToDate(
        [Date].[Fiscal].[Fiscal Year]
        ,[Date].[Fiscal].CurrentMember
    )
    ,[Measures].[Budget]
)

So when we have a month slicer to select the month, if the end-user selects more then 1 month, then the CurrentMember fails and the parent is used, so the Full Year (FY) is used.

Your questions is not clear. Try adding some ore context to it. — SouravA
If I understand correctly you are trying to select MAX from dimension which should be something similar to stackoverflow.com/questions/12532704/… — singhswat
The above calc works correctly if a single value is selected from the date dimension, however if the end-user selects a range of dates (so Jan-16 and Feb-16), which they can easily do via slicers in Excel. This breaks. The CurrentMember doesn't work, it can only handle a single value. So it fails back to the parent - so this will then return the FY value rather then the YTD value. Best solution provided off-topic was to create a dynamic sets — Matt Smith

Mah Mah · Accepted Answer · 2018-01-26T15:16:50

It might be very late to answer this question but hopefully someone else can benefit with the answer.

for this particualr problem you have to create a dynamic set and apply scope to that. check the below MDX.

dynamic set : existing PeriodsToDate(Date].[Fiscal].[Fiscal Year],[Date].
[Fiscal].CurrentMember)

calcualted member : Aggregate( PeriodsToDate( [Date].[Fiscal].[Fiscal Year] ,[Date].[Fiscal].CurrentMember ) ,[Measures].[Budget] ) apply scope to it scope (Date].[Fiscal].[Fiscal Year],,[Measures].[Budget]) this = aggregate (([dynamic set],],[Measures].[Budget]) end scope

this way u can select a date range without any problem

Kind Regards mah

SSAS MDX - PeriodsToDate not working when user selects multiple dates

1 Answers