Search code examples
ssasmdx

MDX: Moving average calculation only gives (null) results

I am trying to compute a moving average using MDX in SSAS 2014,but all results come out (null).

Reading multiple web references and blog posts makes me think that this should work, giving a 3-month moving average:

With
  MEMBER [Measures].[MA3] AS 
    Avg(
         [Date].[Calendar Years].[Month].CurrentMember.Lag(2) 
          : [Date].[Calendar Years].[Month],
         [Measures].[Project Views]
       )

SELECT  { [Measures].[Project Views], [Measures].[MA3] } ON 0,
          [Date].[Calendar Years].[Month]  ON 1
FROM [ProjectAccesses]

However, (null) appears in each column.

I'd expect the count of members to be 3 in every case using:

  MEMBER  [Measures].[C3] AS 
    Count(
          [Date].[Calendar Years].[Month].CurrentMember.Lag(2) 
             : [Date].[Calendar Years].[Month],
          INCLUDEEMPTY
         )

but again, no syntax error is detected but all values are (null), leading me to believe that maybe the month-range sets are empty.

I've been around and around this for hours now and have run out of things to try.

Can anyone suggest what might be going wrong please; I'm a newcomer to MDX so it's possible that my query is wrongly structured or there is some error in my cube configuration that I have not been able to find.

Thanks

Solution

  • I'm surprised that you cannot use the Calendar Years hierarchy throughout. I've added a couple of extra diagnostic measures so you can see what the currentmember function is returning:

    WITH
  MEMBER [Measures].[MA3] AS 
      AVG(
           [Date].[Calendar Years].CurrentMember.Lag(2) 
             : [Date].[Calendar Years].CurrentMember,
           [Measures].[Project Views]
         )
  MEMBER  [Measures].[C3] AS 
    COUNT(
           [Date].[Calendar Years].CurrentMember.Lag(2) 
             : [Date].[Calendar Years].CurrentMember,
           INCLUDEEMPTY
         )
  MEMBER  [Measures].[Diagn1] AS  
    [Date].[Calendar Years].CurrentMember.member_caption
  MEMBER  [Measures].[Diagn2] AS  
    [Date].[Calendar Years].CurrentMember.Lag(2).member_caption
SELECT  
    { 
      [Measures].[Project Views]
    , [Measures].[MA3] 
    , [Measures].[C3] 
    , [Measures].[Diagn1]
    , [Measures].[Diagn2]
    } ON 0,
    [Date].[Calendar Years].[Month].MEMBERS  ON 1
FROM [ProjectAccesses];