In many companies people want to compare todays sales with the comparable date last year. To find out what date the comparable day is we usually goes 364 days back from today.

In our sample today is 14 february 2012. And to see what the comparable date is we can use this query in SQL.

select '2012/02/14' as TodaysDate, DateName(dw, '2012/02/14') As 'DayName', 
dateadd(day, -364, '2012/02/14') As ComparableDay, DateName(dw, dateadd(day, -364, '2012/02/14')) As ComparableDayName

This will give this result

To check the sales for these days in MDX we can use these queries:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20110215] ON 1
FROM [Adventure Works]

The final query that compares today sales can look like this (using “lag”)

WITH MEMBER [Sales comparable day] AS
  ( [Date].[Calendar].CurrentMember .Lag ( 364 ),
  [Measures].[Internet Sales Amount] ),
  FORMAT_STRING = "Currency"
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Or this (using ParallelPeriod and Aggregate)

WITH MEMBER [Sales comparable day] AS
  Aggregate (
    ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ),
    [Measures].[Internet Sales Amount]
  )
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

These samples can be run against the AdventureWorks cube in Multidimensional mode. To avoid error messages please make sure your time dimension has the members for the date you are comparing with. In this case 15 february 2011 has to exists in the time dimension. The -364 approach will have issues if last year was a leap years.