[SOLVED] MDX dynamic year in filter

MDX dynamic year in filter

I have the following simple MDX fetching quantity from my data cube:

SELECT NON EMPTY
{  
  [Measures].[QTY]
} ON COLUMNS FROM
(
  SELECT
  (
    [DATE].[YEAR].&[2022] : null 
  ) ON COLUMNS FROM [MY_DATA_CUBE]
)

As you can see the lower limit range for DATE.YEAR dimension is here hard coded (2022). There's no upper limit (null) so this is like DATE.YEAR >= 2022.

I'd like to have MDX with dynamic value (current year - 2). How to do this?

Solution

In the old MDX you can use strToMember :

SELECT 
   NON EMPTY {  [Measures].[QTY] } ON COLUMNS 
FROM
(
  SELECT
    { strToMember("[DATE].[YEAR].&["+Format(now(), “yyyy”)+"]") 
      : 
     strToMember("[DATE].[YEAR].&["+Format(now(), “yyyy”)+"]").lag(2) } on COLUMNS
  FROM [MY_DATA_CUBE]
)

In icCube you could some new functions :

SELECT

NON EMPTY { [Measures].[QTY] } ON COLUMNS

FROM [MY_DATA_CUBE]

FILTERBY [DATE].[YEAR].dtAsOfToday() : [DATE].[YEAR].dtAsOfToday().dtMinusYears(2)