MDX query: How to compare year to date figures from the current year with year to date figures from the previous year?

Question

With the below MDX queries I am attempting to return a comparison of sales figures.

I want to compare YTD with LYTD, defined as follows:

YTD: 01 Jan this year (01/Jan/2011) until today (17/Aug/2011)

LYTD: 01 Jan last year (01/Jan/2010) until today's date last year (17/Aug/2010)

Both periods should contain the same number of days (give or take one day for leap years).

So far I have tried using the YTD() function to get this year's data but I have not had much success with wrapping the YTD() function in something to get it to return the same dates from last year. I have employed LAG(1) and ParallelPeriod (see both examples below):

WITH
MEMBER [Measures].[Sales YTD] AS
    'SUM(YTD(),[Measures].[Sales])'
MEMBER [Measures].[Sales LYTD] AS
    'SUM((YTD(),[Measures].[Sales]).LAG(1))'
SELECT
    {[Sales YTD], [Sales LYTD]} ON COLUMNS
    ...

Or

WITH
MEMBER [Measures].[Sales YTD] AS
    'SUM(YTD(),[Measures].[Sales])'
MEMBER [Measures].[Sales LYTD] AS
    'SUM(ParallelPeriod(YTD(),1),[Measures].[Sales])'
SELECT
    {[Sales YTD], [Sales LYTD]} ON COLUMNS
    ...

The resulting data when using either ParallelPeriod or LAG always returns a column of nulls for Last Year's sales:

Customer    Sales YTD  Sales LYTD
ABC Screws      $1024       (Null)
ABC Bolts       $2392       (Null)
ABC Nuts        $3735       (Null)

Can anybody suggest changes to the query which will return the correct data, or perhaps offer an alternative strategy to using LAG or ParallelPeriod?

Max Max · Accepted Answer · 2011-08-17T19:35:36

use something like that:

WITH
        MEMBER [Measures].[Sales YTD] AS
            'SUM(YTD(closingperiod([Date].[Calendar].[Date])),[Measures].[Sales])'
        MEMBER [Measures].[Sales LYTD] AS
            'SUM(YTD(PARALLELPERIOD([Date].[Calendar].[Calendar Year],1,closingperiod([Date].[Calendar].[Date]))),[Measures].[Sales])'
        SELECT
            {[Measures].[Sales YTD],[Measures].[Sales LYTD]} ON COLUMNS
        ...

, where

[Date].[Calendar].[Date] - level "Day" in your Date dimension;
closingperiod([Date].[Calendar].[Date]) - last member of this level "Day" (17/Aug/2011);
[Date].[Calendar].[Calendar Year] - level "Year" in your Date dimension;
PARALLELPERIOD([Date].[Calendar].[Calendar Year],1,closingperiod([Date].[Calendar].[Date])) - 17/Aug/2010.

MDX query: How to compare year to date figures from the current year with year to date figures from the previous year?

2 Answers