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