Reputation: 903
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:
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
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