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]}

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]

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]

• SSIS/SSAS/SSRS/MDX