MDX Time Calculation example Last Year equivalent week day

This example runs on the adventureworks 2008 R2 database.

It returns last year sales aligning week days ie Last years equivalent Saturday with this years equivalent Saturday etc (generally the type of comparison that a retailer might be interested in).

If the 31 Days of July 2008 are selected then the LY equivalent will also be 31 days, but lining week days up ie it will be 2 July to 1 August as it works exactly 52 weeks back – 364 days.


WITH MEMBER [Measures].[LY Internet Sales Amount]
AS
CASE
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 0 THEN "NA" --All Member
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 1 --Calendar Year
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.FirstChild.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.LastChild.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 2 --Calendar Semester
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
	WHEN 
	[Date].[Calendar].CurrentMember.Level.Ordinal = 4 --Calendar Month
	THEN 
	SUM({[Date].[Calendar].CurrentMember.FirstChild.Lag(364):
	[Date].[Calendar].CurrentMember.LastChild.Lag(364)}, 
	[Measures].[Internet Sales Amount])
ELSE
	SUM([Date].[Calendar].CurrentMember.Lag(364), 
	[Measures].[Internet Sales Amount])
END, FORMAT_STRING = "Currency"

SELECT
NON EMPTY
(
	[Date].[Calendar].allmembers
)
ON ROWS,
(
{
	[Measures].[Internet Sales Amount],
	[Measures].[LY Internet Sales Amount]
}
)
ON COLUMNS
FROM
	[Adventure Works]

Advertisements

About ditchie2013

A Microsoft SQL Server Analysis Services developer.
This entry was posted in Analysis Services, MDX Examples, MDX Time calculations. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s