Posted: 1/16/2012
I am new to SSAS with no training and no support. A consulting firm helped us with the MDX, but that's that. Our calendar year works from July to June, but not the 1st. Last year 27/06/2010 to 25/06/2011 and current year from 26/06/2011 till 30/06/2012. Thus ParallelPeriod does not work for us. Our sales data is weekly data with date of the Saturday The problem : The measure works fine if you select 1 Year/Quarter/Month/Week, but as soon as you select more than 1 Year/Quarter/Month/Week we get nothing.
The MDX code as in BIDS :
CREATE HIDDEN [PY WEEK MDX];[Measures].[PY WEEK MDX] =CASE-- Days - get the week formula using ParentWHEN [TIME].[Calendar].CurrentMember.Level.name = "DAY"THEN [TIME].[Calendar].CurrentMember.Parent.Properties("PYR WK NO MDX")-- Week - get the week formulaWHEN [TIME].[Calendar].CurrentMember.Level.name = "WEEK"THEN [TIME].[Calendar].CurrentMember.Properties("PYR WK NO MDX")ELSE NULLEND;
-- DAY POSITION WITHIN THE WEEKCREATE HIDDEN[DAY POS];[Measures].[DAY POS] = IIF ([TIME].[Calendar].CurrentMember.Level.name = "DAY", [TIME].[Calendar].CurrentMember.Properties("DAY NO"), NULL);
CREATE MEMBER CURRENTCUBE.[Measures].[Last Years Sales Incl]AS CASE-- DayWHEN [TIME].[Calendar].CurrentMember.Level.name = "DAY"THEN ([Measures].[Sales MDB Incl],StrToMember(CStr([Measures].[PY WEEK MDX].Value)), StrToMember("[TIME].[DAY NO].[DAY NO].&[" + CStr([Measures].[DAY POS].Value) + "]"))-- WeeksWHEN [TIME].[Calendar].CurrentMember.Level.name = "WEEK"THEN ([Measures].[Sales MDB Incl],StrToMember(CStr([Measures].[PY WEEK MDX].Value)))-- Recurive to sum up the weeksWHEN [TIME].[Calendar].CurrentMember.Level.name = "MONTH"THEN SUM([TIME].[Calendar].CurrentMember.Children, [Measures].[Last Years Sales Incl])-- Normal ParallelPeriodELSE(ParallelPeriod([TIME].[Calendar].[YEAR], 1), ([Measures].[Sales MDB Incl]))END,FORMAT_STRING ="R#,##0.00;R-#,##0.00",VISIBLE = 1;
See attached for MDX code as well as the Calendar table.