MDX Calculated Measure summing across all of time

Question

I have developed an MDX query for a SSRS report that has a calculated measure called CurrentMonthAndAllOfLastYear which SHOULD sum the current month's net sales together with the net sales from the previous year. Unfortunately the calculated measure seems to sum every net sales amount for all of time. The MDX is:

WITH 
SET [CurrMonth] AS [Sales Period].[Sales Period].CurrentMember --Month
SET [CurrYear] AS ANCESTORS([CurrMonth](0), 2)   --Year
SET [PriorYear] AS [CurrYear](0).LAG(1)   -- Gets prior Year

MEMBER [Measures].[CurrentMonthAndAllOfLastYearAmt] AS SUM({[CurrMonth], [PriorYear]}, Measures].[Net Sales])
MEMBER [Measures].[Debtors Collection Days] AS ([Measures].[Total Owing Amount] * 90) / [Measures].[CurrentMonthAndAllOfLastYearAmt]

SELECT  
{
[Measures].[CurrentMonthAndAllOfLastYearAmt],
[Measures].[Total Owing Amount],
[Measures].[Debtors Collection Days],
[Measures].[Net Sales] } ON COLUMNS,
{
[Sales Period].[Sales Month].[Sales Month].ALLMEMBERS } ON ROWS 
FROM ( SELECT ( YTD(STRTOMEMBER('[Sales Period].[Sales Period].[Sales Month].&[2013-05-31T00:00:00]')) ) ON COLUMNS 
FROM ( SELECT ( STRTOSET('[Sales Representative Mirror].[Sales Representative ID].&[261]') ) ON COLUMNS 
FROM [Sales]))

The MDX snippet produces the following:

Sales Month CurrentMonthAndAllOfLastYearAmt 
31/01/2013  $320,429.60 ... 
28/02/2013  $320,429.60 ... 
31/03/2013  $320,429.60 ... 
30/04/2013  $320,429.60 ... 
31/05/2013  $320,429.60 ...

I require the following output (there were not any sales in 2012):

Sales Month  CurrentMonthAndAllOfLastYearAmt
31/01/2013  $0.00 ...
28/02/2013  $0.00 ...
31/03/2013  $0.00 ...
30/04/2013  $18,931.10 ...
31/05/2013  $22,557.22 ...

Any help would be much appreciated.

ic3 ic3 · Accepted Answer · 2014-06-16T08:16:52

I would not use a set for performing this operation but use the ParallelPeriod MDX function.

MEMBER [Measures].[CurrentMonthAndAllOfLastYearAmt] AS 
   ([Sales Period].[Sales Period].CurrentMember,[Measures].[Net Sales]) 
   + 
   ( ParallelPeriod( [CurrMonth](0), 1, [Sales Period].[Sales Period].CurrentMember), [Measures].[Net Sales] )

Good question is the set is evaluated each time it is used or once per MDX request. After that you've the context (using or not where and subquery context).

MDX Calculated Measure summing across all of time

1 Answers