Requirement: Users specify a date range by giving a Start Date and a End Date, and expect to make Year-Over-Year comparison of a measure (e.g. Sales Amount) in the given period. For example, a user input start date as “04/07/2007” and end date as “04/11/2007”, and want to compare the sales between the period “04/07/2007 – 04/11/2007” and the same period in last year “04/07/2006 – 04/11/2006”.

First, we need get the data range using the range operator ‘:’ on the given start date and end date, and aggregate the sales occurred within that period.

MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

Then we could use PARALLELPERIOD to get the same period in last year. Please note that we could use range operator within a PARALLELPERIOD function, so we have to separately PARALLELPERIOD the start date and the end date, and use range operator afterwards.

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060211]")
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060911]")
)
}
, [Measures].[Reseller Sales Amount]
)

Below is the complete script:

WITH
MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@StartDate, CONSTRAINED)
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@EndDate,  CONSTRAINED)
)
}
, [Measures].[Reseller Sales Amount]
)

SELECT {
[Measures].[Sales in Given Period],
[Measures].[Sales in Given Period Last Year]
} ON COLUMNS,
{
[Product].[Product Categories].[Category]
} ON ROWS
FROM [Adventure Works]

Tip: when using STRTOMEMBER function to convert the data string into Member value, the data string has to be in correct format. We could directly pass data parameter into MDX query and build the data string to the correct format in the MDX script. However, a better way is to build the data string at SSRS report level (using expression in the parameter of dataset) as the snapshot shown below:

• SSIS/SSAS/SSRS/MDX