How to query 3 month running average from SSAS Multidimensional Cube (MDX) for each date?

Question

I have SSAS Multidimensional Cube and I need to query 3 month running average for each day in date range.

For example, for 2016-04-25 I must to get data from 2016-01-01 to 2016-03-31. So I can't use this query (because I don't know how much days I must lag till previous month):

WITH MEMBER [Measures].[SalesAmount 3m average] AS
(
SUM(
    ([Date].[Date].CurrentMember.Lag(90) :
    ([Date].[Date].CurrentMember.Lag(1),
    [Measures].[SalesAmount]
    )
)

I guess I need to use Ancestor function to get month and use lag to month granularity.

Ok, let's try this one:

WITH MEMBER [Measures].[SalesAmount 3m average] AS
(
SUM(
    (Ancestor ( [Date].[Date].CurrentMember, [Date].[Month] )).Lag(3) :
    (Ancestor ( [Date].[Date].CurrentMember, [Date].[Month] )).Lag(1),
    [Measures].[SalesAmount]
    )
)
SELECT { [Measures].[SalesAmount 3m average] } ON Columns,
{ [Date].[Date].&[2016-01-01T00:00:00] : [Date].[Date].&[2016-02-28T00:00:00]} On Rows
FROM [Cube]

Unfortunately, this query doesn't work properly (returns null).

How to solve this problem?

UPDATED: Ok, I tryed to query member caption:

MEMBER [Measures].[Test] AS
(
(Ancestor 
    (   [Date].[Date].CurrentMember, 
        [Date].[Date].[Month] )
    ).Item(0).Member_Caption
)

And I'm getting not a Month caption, but a Date(Day) - the same date as [Date].[Date].CurrentMember.

Then I tryed this queries:

--First try
MEMBER [Measures].[Test] AS
(
IsAncestor([Date].[Date].CurrentMember, [Date].[Month].&[2016-05-01T00:00:00])
) 

--Second try
MEMBER [Measures].[Test] AS
(
IsAncestor([Date].[Date].CurrentMember, [Date].[Date].[Month].&[2016-05-01T00:00:00])
) 

--Third try
MEMBER [Measures].[Test] AS
(
IsAncestor([Date].[Date].CurrentMember, [Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-05-01T00:00:00])
)

In all queries result was "False". So, Month member not an ancestor to Date members??? Now I'm really confused.

My Date dimension looks like this:

UPDATE 2:

MEMBER [Measures].[Test] AS
(
(Ancestor 
    (   [Date].[Date].CurrentMember, 
        1 )
    ).Member_Caption
)

Returns: All

ok - the test have given us the answer - I will update my answer — whytheq

whytheq whytheq · Accepted Answer · 2016-04-25T08:41:06

Your code seems fine. I'd add item(0) in a couple of places to make things more obvious but this shouldn't change much

WITH 
  MEMBER [Measures].[SalesAmount 3m average] AS 
    Sum
    (
        Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Month]
        ).Item(0).Lag(3)
      : 
        Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Month]
        ).Item(0).Lag(1)
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[SalesAmount 3m average]} ON COLUMNS
 ,{
      [Date].[Calendar].[Date].&[20060222]
    : 
      [Date].[Calendar].[Date].&[20060722]
  } ON ROWS
FROM [Adventure Works];

Returns this:

Check that all your level expressions are correct:

[Date].[Date] ?

and

[Date].[Month] ?

Also as a further test what does this return?

WITH 
  MEMBER [Measures].[test1] AS 
    Ancestor
    (
      [Date].[Date].CurrentMember
     ,[Date].[Month]
    ).Item(0).Lag(3).Member_Caption 
  MEMBER [Measures].[test2] AS 
    Ancestor
    (
      [Date].[Date].CurrentMember
     ,[Date].[Month]
    ).Item(0).Lag(1).Member_Caption 
SELECT 
  {
    [Measures].[test1]
   ,[Measures].[test2]
  } ON COLUMNS
 ,{
      [Date].[Date].&[2016-01-01T00:00:00]
    : 
      [Date].[Date].&[2016-02-28T00:00:00]
  } ON ROWS
FROM [Cube];

Ancestor will function between different levels of the same hierarchy ie:

What this means is that [Date].[Date] and [Date].[Month] are not really related via Ancestor. Try this:

WITH 
MEMBER [Measures].[SalesAmount 3m average] AS
  (
    SUM(
      Ancestor ( 
         [Date].[Year -  Quarter -  Month -  Date].CurrentMember,
         [Date].[Year -  Quarter -  Month -  Date].[Month] ).Lag(3) 
       :
      Ancestor( 
         [Date].[Year -  Quarter -  Month -  Date].CurrentMember
        ,[Date].[Year -  Quarter -  Month -  Date].[Month] ).Lag(1)
      ,[Measures].[SalesAmount]
    )
  )
SELECT 
{ [Measures].[SalesAmount 3m average] } ON Columns,
{ 
  [Date].[Year -  Quarter -  Month -  Date].[Date].&[2016-01-01T00:00:00] 
: [Date].[Year -  Quarter -  Month -  Date].[Date].&[2016-02-28T00:00:00]
//I suspect the above 2 lines can be replaced by following:
//  [Date].[Date].&[2016-01-01T00:00:00]
//: [Date].[Date].&[2016-02-28T00:00:00]
}  
  On Rows
FROM [Cube];

note

This should return All because the hierarchy [Date].[Date] only has two levels ... the leaf level are the dates with a single level above that All:

MEMBER [Measures].[Test] AS
(
(Ancestor 
    (   [Date].[Date].CurrentMember, 
        1 )
    ).Member_Caption
)

How to query 3 month running average from SSAS Multidimensional Cube (MDX) for each date?

1 Answers