Now() in where clause in MDX query

Question

I am trying to find out all the records between current date -30 days. But not able to write a MDX filter for this. My current query is:

 WITH
  SET [~FILTER] AS
     {[Created_Date.Created_Hir].[Created_On].Members}
 SET [~ROWS] AS
    {[Sales Order Attributes SO.Sales_order].[Sales Order ID].Members}
    SELECT
   NON EMPTY {[Measures].[CONT_AMT_GROSS]} ON COLUMNS,
   NON EMPTY [~ROWS] ON ROWS
   FROM [SALES_ORDER]
   WHERE [~FILTER]

which is fetching all the records.

Answer 1

Here is an example of finding today in the AdvWrks cube along with the 30 preceding days:

WITH 
  MEMBER [Measures].[Key for Today] AS 
    Format
    (
      Now()
     ,'yyyyMMdd'
    ) 
  MEMBER [Measures].[Key for Today (AW)] AS 
    '2007' + Right([Measures].[Key for Today],4) 
  MEMBER [Measures].[Today string] AS 
    '[Date].[Calendar].[Date].&[' + [Measures].[Key for Today (AW)] + ']' 
  SET [Today] AS 
    StrToMember
    (
      [Measures].[Today string]
     ,constrained
    ) 
  SET [Last30Days] AS 
    [Today].Item(0).Item(0).Lag(30) : [Today].Item(0).Item(0) 
SELECT 
  {} ON 0
 ,[Last30Days] ON 1
FROM [Adventure Works];

Results in this:

So if we wanted to amend the above so that we only get Internet Sales Amount for the past 30 days:

WITH 
  MEMBER [Measures].[Key for Today] AS 
    Format
    (
      Now()
     ,'yyyyMMdd'
    ) 
  MEMBER [Measures].[Key for Today (AW)] AS 
    '2007' + Right([Measures].[Key for Today],4) 
  MEMBER [Measures].[Today string] AS 
    '[Date].[Calendar].[Date].&[' + [Measures].[Key for Today (AW)] + ']' 
  SET [Today] AS 
    StrToMember
    (
      [Measures].[Today string]
     ,constrained
    ) 
  SET [Last30Days] AS 
    [Today].Item(0).Item(0).Lag(30) : [Today].Item(0).Item(0) 
  MEMBER [Date].[Calendar].[All].[Last30Days] AS 
    Aggregate([Last30Days]) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
FROM [Adventure Works]
WHERE [Date].[Calendar].[All].[Last30Days];

Applying to your scenario the above would look something like this:

WITH 
  MEMBER [Measures].[Key for Today] AS 
    Format
    (
      Now()
     ,'yyyyMMdd'
    ) 
  MEMBER [Measures].[Today string] AS 
    '[Created_Date.Created_Hir].[Created_On].&[' + [Measures].[Key for Today] + ']' 
  SET [Today] AS 
    StrToMember
    (
      [Measures].[Today string]
     ,constrained
    ) 
  SET [~FILTER] AS 
    [Today].Item(0).Item(0).Lag(30) : [Today].Item(0).Item(0) 
  MEMBER [Created_Date.Created_Hir].[All].[~FILTERaggregated] AS 
  //MEMBER [Created_Date.Created_Hir].[Created_On].[All].[~FILTERaggregated] AS //<<if above is throwing an exception then try this instead
    Aggregate([~FILTER]) 
  SET [~ROWS] AS 
    {
      [Sales Order Attributes SO.Sales_order].[Sales Order ID].MEMBERS
    } 
SELECT 
  NON EMPTY 
    {[Measures].[CONT_AMT_GROSS]} ON COLUMNS
 ,NON EMPTY 
    [~ROWS] ON ROWS
FROM [SALES_ORDER]
WHERE 
  [Created_Date.Created_Hir].[All].[~FILTERaggregated];