WITH
SET WTDCP
--AS StrToMember(@Week)
AS StrToMember("[Time].[Fiscal Hierarchy].&[2013043]")
--YTD
MEMBER [Measures].[First Month of Year]
AS ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalMonth]).PROPERTIES("key", TYPED) = ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalYear]).FIRSTCHILD.FIRSTCHILD.FIRSTCHILD.PROPERTIES("key", TYPED)
SET YTDCP
AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER))
SET YTDPP
AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear],1,Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER)))
-- Start for FID logic
MEMBER [Measures].[FID] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)
--If two brads have different FID because of different currency so puting logic to
--get Minimum of FID out of multiple FID for diff currencies.
MEMBER [Measures].[First Invoice Date]AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FID]),
[Measures].[FID])
--End of logic
MEMBER [Measures].[FIY] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalYear].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)
MEMBER [Measures].[First Invoice Year] AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FIY]),
[Measures].[FIY])
MEMBER [Measures].[Selected Year]
AS Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]).properties("key", TYPED)
SET ALL_Months_Selected_Year
AS Descendants(Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])
Set test
as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')
MEMBER [Measures].[First Invoice Date1]
as test.item(0).name
MEMBER [Measures].[First Invoice Date2]
as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']').name
SET ALL_Months_Till_First_Invoiced_Month
AS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear],
PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,test.item(0)))
SET COMPYTD_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],
YTDCP,
IIF([Measures].[Selected Year] =
[Measures].[First Invoice Year],
NULL,
Intersect(YTDCP, COMPYTD_SET_SUBSTRACT_CP)
))
SELECT {
[Measures].[First Invoice Date],
[Measures].[First Invoice Date1],
[Measures].[First Invoice Date2]
} on columns,
Nonempty( {[Brand].[Brand].[Brand Major].members
* [Customer].[Country].[Country]
* [Currency].[Currency].[Currency]}) on rows
FROM [VFE]