Last Year sales problem

Who is online?  0 guests and 1 members
Home  »  Forums   »  microsoft business intelligence   »  analysis services   » Last Year sales problem

Last Year sales problem

Topic RSS Feed

Posts under the topic: Last Year sales problem

Posted: 1/16/2012

Jedi Youngling 2  points  Jedi Youngling
  • Joined on: 1/16/2012
  • Posts: 1

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 Parent
WHEN [TIME].[Calendar].CurrentMember.Level.name = "DAY"
THEN [TIME].[Calendar].CurrentMember.Parent.Properties("PYR WK NO MDX")
-- Week - get the week formula
WHEN [TIME].[Calendar].CurrentMember.Level.name = "WEEK"
THEN [TIME].[Calendar].CurrentMember.Properties("PYR WK NO MDX")
ELSE NULL
END;

-- DAY POSITION WITHIN THE WEEK
CREATE 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
-- Day
WHEN [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) + "]"))
-- Weeks
WHEN [TIME].[Calendar].CurrentMember.Level.name = "WEEK"
THEN ([Measures].[Sales MDB Incl],StrToMember(CStr([Measures].[PY WEEK MDX].Value)))
-- Recurive to sum up the weeks
WHEN [TIME].[Calendar].CurrentMember.Level.name = "MONTH"
THEN SUM([TIME].[Calendar].CurrentMember.Children, [Measures].[Last Years Sales Incl])
-- Normal ParallelPeriod
ELSE
(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.


Page 1 of 1 (1 items)