MDX YTD over range (:)

Question

Does YTD can handle date range ?

I'm trying to compare a period of X days of the current year with the same period of the prior year.

My MDX query looks like this :

WITH MEMBER [Measures].[Prior YTD Amount] AS SUM
(
   YTD
   (
      ParallelPeriod
      (
         [Date].[Year],
         1,
         [Date].CurrentMember
      )
   )
   ,
   [Measures].[Amount]
)
set [ColSet] as
{
   [Measures].[Amount],
   [Measures].[Prior YTD Amount]
}
set [RowSet] as
{
   [Motif].[Categorie].Members
}
SELECT
NON EMPTY [ColSet] ON COLUMNS,
NON EMPTY [RowSet] ON ROWS
FROM [Things]
WHERE
{
   [Date].[2015].[2].[1]:[Date].[2015].[2].[9]
}

I'm working with Mondrian 3.6.

Answer 1

In short this code will not work. Reason being that the following line of code is looking for a single member of the hierarchy you've applied the Currentmember to:

[Date].CurrentMember

You have more than one member in the set [Date].[2015].[2].[1]:[Date].[2015].[2].[9]

---

Here is a small proof of the above:

This script:

WITH 
  MEMBER [Measures].[x] AS 
    [Date].[Calendar].CurrentMember.Member_Key 
SELECT 
  {[Product].[Category].[All Products]} ON 0
 ,{[Measures].[x]} ON 1
FROM [Adventure Works]
WHERE 
  {
      [Date].[Calendar].[Date].&[20080322]
  };

Returns this:

Whereas this script:

WITH 
  MEMBER [Measures].[x] AS 
    [Date].[Calendar].CurrentMember.Member_Caption 
SELECT 
  {[Product].[Category].[All Products]} ON 0
 ,{[Measures].[x]} ON 1
FROM [Adventure Works]
WHERE 
  {
      [Date].[Calendar].[Date].&[20080322]
    : 
      [Date].[Calendar].[Date].&[20080323]
  };

Returns this:

Error message:

---

In AdvWrks I just wrote the following which might help, if you can move your target range of dates from the WHERE clause to a named set.

How it works:

1.Finds the first date in your named set via .Item(0).Item(0)
2.Finds the last date in your named set via .Item([myStartDates].Count - 1).Item(0)
3.The uses the two dates found to create a range that is parallel

WITH 
  SET [myStartDates] AS 
    {
        [Date].[Calendar].[Date].&[20080322]
      : 
        [Date].[Calendar].[Date].&[20080323]
    } 
  SET [parallelToFirst] AS 
    ParallelPeriod
    (
      [Date].[Calendar].[Calendar Year]
     ,1
     ,[myStartDates].Item(0).Item(0)
    ) 
  SET [parallelToLast] AS 
    ParallelPeriod
    (
      [Date].[Calendar].[Calendar Year]
     ,1
     ,[myStartDates].Item(
      [myStartDates].Count - 1).Item(0)
    ) 
  SET [Rebuild] AS 
    [parallelToFirst].Item(0) : [parallelToLast].Item(0) 
SELECT 
  {} ON COLUMNS
 ,[Rebuild] ON ROWS
FROM [Adventure Works];

Easy enough to then Sum that range and pop it in the date dimension:

WITH 
  SET [myStartDates] AS 
    {
        [Date].[Calendar].[Date].&[20080322]
      : 
        [Date].[Calendar].[Date].&[20080323]
    } 
  SET [parallelToFirst] AS 
    ParallelPeriod
    (
      [Date].[Calendar].[Calendar Year]
     ,1
     ,[myStartDates].Item(0).Item(0)
    ) 
  SET [parallelToLast] AS 
    ParallelPeriod
    (
      [Date].[Calendar].[Calendar Year]
     ,1
     ,[myStartDates].Item(
      [myStartDates].Count - 1).Item(0)
    ) 
  SET [ParallelRange] AS 
    [parallelToFirst].Item(0) : [parallelToLast].Item(0) 
  MEMBER [Date].[Calendar].[parallelSum] AS 
    Sum([ParallelRange]) 
SELECT 
  {
    [myStartDates]
   ,[ParallelRange]
   ,[Date].[Calendar].[parallelSum]
  } ON 0
 ,{
    [Measures].[Internet Sales Amount]
   ,[Measures].[Internet Order Quantity]
  } ON 1
FROM [Adventure Works];

Here is the result (just to prove it is doing what we want):