October 8, 2015 at 2:02 pm
I created a Named Set for what I wanted to be a rolling 12 Month range using the code below.
When I attempt to use the set in a Calculated Member I do not get the results I expect. For example the two Calculated Members below give different results:
with
set [Last Twelve Months] as
{ParallelPeriod( [Calendar].[Calendar Month].[Calendar Month], 11, [Calendar].[Calendar Month].currentmember )
:
[Calendar].[Calendar Month].currentmember }
member [Measures].[Sales LTM] as
sum(
[Last Twelve Months]
,[Measures].[Store Sales])
member [Measures].[Sales LTM 2] as
sum(
{ParallelPeriod( [Calendar].[Calendar Month].[Calendar Month], 11, [Calendar].[Calendar Month].currentmember )
:
[Calendar].[Calendar Month].currentmember }
,[Measures].[Store Sales])
select
{
[Measures].[Sales LTM]
,[Measures].[Sales LTM 2]
}
on columns,
{
[Calendar].[Calendar Month].&[201509]
}
on rows
from [Customer Datamart]
I am having difficulty understanding why the Calculation with the Named Set does not appear to get the context for CurrentMember, instead it returns the sum of all Sales ignoring the Date range. the second Calculation does what I expected, returning the Sales for the rolling 12 month period. Can anyone explain what is going on with this?
October 9, 2015 at 6:17 am
I believe that Sets do not evaluate CURRENTMEMBER in the query context so it's a bit like trying to sum to everything in a dimension.
Just do the following:
MEMBER [Measures].[Sales LTM] AS
SUM(
{[Calendar].[Calendar Month].CURRENTMEMBER.LAG(12): [Calendar].[Calendar Month].CURRENTMEMBER} //This is a little simpler than using ParallelPeriod
,[Measures].[Store Sales])
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply