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]