Firstly, for the love of all that is holy please format your code and put it in a code block before posting it here 😀
WITH
SET SP AS
StrToMember([Time].[Fiscal Hierarchy].&[201204])
MEMBER [Measures].[First Invoice Year] AS
Head
(
NonEmpty
(
(EXISTING
[Time].[Fiscal Hierarchy].[FiscalYear].MEMBERS)
,[Measures].[Sales Quantity]
)
,1
).Item(0).Properties("key"
,TYPED)
SET ALL_Months_Selected_Year AS
Descendants
(
Ancestor
(
SP.Item(0)
,[Time].[Fiscal Hierarchy].[FiscalYear]
)
,[Time].[Fiscal Hierarchy].[FiscalMonth]
)
//--Getting Month previous to the First Invoice Month and then getting PeriodsToDates
SET ALL_Months_Till_First_Invoiced_Month AS
PeriodsToDate
(
[Time].[Fiscal Hierarchy].[FiscalYear]
,ParallelPeriod
(
[Time].[Fiscal Hierarchy].[FiscalYear]
,-1
,Head
(
NonEmpty
(
(EXISTING
[Time].[Fiscal Hierarchy].[FiscalMonth].MEMBERS)
,[Measures].[JBA Sales Quantity]
)
,1
).Item(0)
)
)
SET COMP_SET_SUBSTRACT_CP AS
Except
(
ALL_Months_Selected_Year
,ALL_Months_Till_First_Invoiced_Month
)
SET COMP_YTD_CP AS
IIF
(
[Measures].[Selected Year] - 1 > [Measures].[First Invoice Year]
,SP.Item(0)
,IIF
(
[Measures].[Selected Year] = [Measures].[First Invoice Year]
,NULL
,Intersect
(
SP
,COMP_SET_SUBSTRACT_CP
)
)
)
MEMBER [MEASURES].[SALES YTD CP COMP] AS
Sum
(
COMP_YTD_CP
,[Measures].[Revenues]
)
SELECT
{[MEASURES].[SALES YTD CP COMP]} ON COLUMNS
,NonEmpty
(
{
[Brand].[Brand].[Brand Major].MEMBERS*
[Customer].[Country].[Country]*
[Currency].[Currency].[Currency]
}
) ON ROWS
FROM [Cube1];
[/Code]
That's better.
Right, this query is pretty static. How are the users viewing the results (excel, a parametrized report etc.) as this will help us answer your question?
On first glance your calculations and sets don't appear to take brand into account. This could be it. However without knowing how the cube is being accessed (and the query that is being sent to the cube) it's difficult to tell.
Download MDX Studio for formatting and much more.