CODEWITHSUNDEEP
sqlssasmdx
Oblomingo
Oblomingo

Reputation: 698

How to query 12 month running average from SSAS Multidimensional Cube (MDX) with date range?

I want to get sales data with 12 month running average. I wrote this MDX query:

WITH MEMBER [Measures].[12m average] AS
Avg
(
[Date].[Month].CurrentMember.Lag(11) :
[Date].[Month].CurrentMember,
 [Measures].[Sales Quantity]
)
SELECT
{ [Measures].[Sales Quantity], [Measures].[12m average] } ON COLUMNS,
NON EMPTY { CROSSJOIN([Product].[Product Name].Allmembers, [Date].[Month].AllMembers) } ON ROWS
FROM [Sales]
where { [Date].[Date].&[2016-01-01T00:00:00] : [Date].[Date].&[2016-02-28T00:00:00] }

Query returns correct Sales Quantity aggregation, but 12m average is incorrect. Average can't get last 12 month data because of where date range.

For example, for 2016-01 month 12m average we need to get 2015-01 - 2016-01 data, but date rage from 2016-01-01T00:00:00.

How to solve this problem?

Upvotes: 1

Views: 886

Answers (2)

whytheq
whytheq

Reputation: 35605

As Tab says the range in the WHERE can move:

WITH 
SET [TargetMths] AS
   EXISTS(
     [Date].[Month].AllMembers
    ,[Date].[Date].&[2016-01-01T00:00:00] : [Date].[Date].&[2016-02-28T00:00:00]
   )
MEMBER [Measures].[12m average] AS
  Avg(
    [Date].[Month].CurrentMember.Lag(11):[Date].[Month].CurrentMember,
    [Measures].[Sales Quantity]
  )
SELECT
  { 
    [Measures].[Sales Quantity]
   ,[Measures].[12m average] 
  } ON 0,
NON EMPTY 
    [Product].[Product Name].Allmembers
  * [TargetMths] ON 1
FROM [Sales];

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31795

Take the Date Range out of the WHERE clause, and make your [Sales Quantity] a calculated measure that uses that date range.

Upvotes: 0

Related Questions