MDX Time calculation example LY equivalent day

This example is done on the adventureworks 2008 R2 cube.

It shows how to calculate the LY equivalent sales lining the same day of the previous year up with the current day.

Example: LY sales for 20130515 will be the same sales that happened on 20120515.

This code takes care of leap years.

Example: LY Sales of 20080229 will be the same sales that happened on 20070228 and LY Sales for 20080228 will also be the same sales that happened on 20070228.


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.Lag(1), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 2 --Calendar Semester
	THEN SUM([Date].[Calendar].CurrentMember.Lag(2), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN SUM([Date].[Calendar].CurrentMember.Lag(4), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 3 --Calendar Quarter
	THEN SUM([Date].[Calendar].CurrentMember.Lag(4), 
	[Measures].[Internet Sales Amount])
	WHEN [Date].[Calendar].CurrentMember.Level.Ordinal = 4 --Calendar Month
	THEN SUM([Date].[Calendar].CurrentMember.Lag(12), 
	[Measures].[Internet Sales Amount])
	WHEN
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") 
	/ 10000) / 4) * 4 = 
	INT([Date].[Calendar].CurrentMember.properties("Key") / 10000) AND
	INT([Date].[Calendar].CurrentMember.properties("Key")) - 
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") 
	/ 10000) * 10000) > 228 --29 Feb to 31 Dec on a Leap year
	THEN 
	SUM([Date].[Calendar].CurrentMember.Lag(366), 
	[Measures].[Internet Sales Amount]) --* 366 days in a leap year 
	WHEN
	INT([Date].[Calendar].CurrentMember.properties("Key") / 10000) / 4 -
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") / 10000) / 4)  = 0.25 AND
	INT([Date].[Calendar].CurrentMember.properties("Key")) -
	INT(INT([Date].[Calendar].CurrentMember.properties("Key") 
	/ 10000) * 10000) <= 228 --1 Jan to 28 Feb following a Leap year
	THEN
	SUM([Date].[Calendar].CurrentMember.Lag(366), 
	[Measures].[Internet Sales Amount]) --* 366 days in a leap year 
ELSE
	SUM([Date].[Calendar].CurrentMember.Lag(365), 
	[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