CODEWITHSUNDEEP
sql-serverreporting-servicesssasmdx
Ben
Ben

Reputation: 903

ParallelPeriod in MDX Query with CurrentMember

I am trying to predict the number of rooms that my organisation will need to book on the basis of how many students we have enrolled for the busy part of our year.

The first part of this problem is calculating the relative decrease/increase in student numbers in this year vs. last year and using that to extrapolate student numbers for this year.

To this end, I am trying to measure the following:

  1. A running total of enrollments in the weeks of the year leading up to a range of start dates e.g. how many students enrolled in weeks 1,2,3... of 2016 who will be here between October and December for a given year
  2. Calculate the percentage increase/decrease over the previous years in student numbers

I have the following query:

WITH MEMBER [Measures].[Period Growth] AS
(
    [Measures].[Enrolments By Week],
    ParallelPeriod([Weekly Enrolments Date].[ISO Week Calendar].[ISO Year],
        1,
        [Weekly Enrolments Date].[ISO Week Calendar].[ISO Week].currentmember)
)
SELECT NON EMPTY { [Measures].[Enrolments By Week], [Measures].[Period Growth] } ON COLUMNS, 
 NON EMPTY { FILTER([Weekly Enrolments Date].[ISO Week Calendar].[ISO Week].&[201738] : 
                    [Weekly Enrolments Date].[ISO Week Calendar].[ISO Week].&[201752],
        Cint([Term Record Creation].[ISO Week Number Of Year].CurrentMember.Member_Key) <= 10 --Cint( STRTOMEMBER(@ToISOWeekNumberOfYear, CONSTRAINED).Member_Key )
        OR
        Cint([Term Record Creation].[ISO Year].CurrentMember.Member_key) < 
        Cint([Term Start Date].[ISO Year].CurrentMember.Member_key)) } ON ROWS 
 FROM [Enrolments]

However, I am getting #Error for the calculated member when I set the member expression in the ParallelPeriod as CurrentMember. If I change the CurrentMember to a value like .&[201642] the error disappears - why is this? How can I get the same week in the previous year as a comparison for each week row of the current year?

In addition, how can I get the percentage change for each week relative to the previous year in the same week, while avoiding divide by 0 errors?

Upvotes: 0

Views: 929

Answers (1)

whytheq
whytheq

Reputation: 35605

To me this looks like a level:

[Weekly Enrolments Date].[ISO Week Calendar].[ISO Week]

So yes this is valid and there will not be an error:

[Weekly Enrolments Date].[ISO Week Calendar].[ISO Week].&[201642]

But I don't think the CURRENTMEMBER function can be applied to a level expression - if you just shorten to the following it might be happier:

[Weekly Enrolments Date].[ISO Week Calendar].currentmember

Upvotes: 1

Related Questions