MDX #1 – Year-Over-Year Comparison

1. Monthly or Quarterly Year-Over-Year Comparision

PARALLELPERIOD is the function to get the same period in previous year.

WITH
	MEMBER [Measures].[SaleInCurrentYear] AS
		AGGREGATE(
			[Date].[Calendar].currentmember,
			[Measures].[Reseller Sales Amount]
		)

	MEMBER [Measures].[SaleInPreviousYear] AS
		AGGREGATE(
			PARALLELPERIOD(
				[Date].[Calendar].[Calendar Year],
				1,
				[Date].[Calendar].currentmember
			)
			, [Measures].[Reseller Sales Amount]
		)

	MEMBER [Measures].[Sales Growth] AS
		IIF ([SaleInPreviousYear]=0,
				NULL,
				([Measures].[SaleInCurrentYear] - [SaleInPreviousYear])
				/[SaleInPreviousYear]
			)
		, FORMAT_STRING="##%" 

SELECT
	{
		[Measures].[SaleInCurrentYear],
		[Measures].[SaleInPreviousYear],
		[Measures].[Sales Growth]
	 } ON COLUMNS,
	 {
		[Date].[Calendar].[Month].MEMBERS
	 } ON ROWS
FROM [Adventure Works]
WHERE {[Date].[Calendar Year].&[2007]}

MF1

2. YTD Year-Over-Year Comparison

YTD function can be used to get the period from start of this year to current date. To get the same YTD period in last year, we could combine the YTD and PARALLELPERIOD used above.

With
	MEMBER [Measures].[YTD Sales] AS
		AGGREGATE (
            YTD([Date].[Calendar].CurrentMember),
            [Measures].[Sales Amount]
        )

	MEMBER [Measures].[Last YTD Sales] AS
		AGGREGATE(
            YTD(
                ParallelPeriod(
                    [Date].[Calendar].[Calendar Year],
                    1,
                    [Date].[Calendar].CurrentMember
                )
            ),
            [Measures].[Sales Amount]
        )

     MEMBER [Measures].[YTD Sales Growth] AS
		[Measures].[YTD Sales] - [Measures].[Last YTD Sales]

SELECT
	{[Measures].[Last YTD Sales],
     [Measures].[YTD Sales],
     [Measures].[YTD Sales Growth]} ON COLUMNS,
    DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2007], 2 ) ON ROWS
FROM [Adventure Works]

MDX04

3. Fiscal YTD Year-Over-Year Comparison

Fiscal year is often not aligned with calendar year, so we could not use YTD but instead using PERIORDTODATE function.

WITH
	MEMBER [Measures].[YTD Sales] AS
		AGGREGATE (
            YTD([Date].[Calendar].CurrentMember),
            [Measures].[Sales Amount]
        )

	MEMBER [Measures].[Last YTD Sales] AS
		AGGREGATE(
            YTD(
                ParallelPeriod(
                    [Date].[Calendar].[Calendar Year],
                    1,
                    [Date].[Calendar].CurrentMember
                )
            ),
            [Measures].[Sales Amount]
        )

     MEMBER [Measures].[YTD Sales Growth] AS
		[Measures].[YTD Sales] - [Measures].[Last YTD Sales]

SELECT
	{[Measures].[Last YTD Sales],
     [Measures].[YTD Sales],
     [Measures].[YTD Sales Growth]} ON COLUMNS,
    DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2007], 2 ) ON ROWS
FROM [Adventure Works]
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s