# 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
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
```

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