MDX - active in current period, and not active in previous period

2
votes

What is the most efficient way to do this in MDX? I know you can create 2 calculated measures, having active with the time dimension current period selected, and another having the previous period, and then do a filter with a complex condition.

However what about other functions which may be more efficient? Any recommendations?

My goal is to create a set of calculated measures, which would help customer analysts. The main 2 dimensions, for these are [Calendar], and obviously [Customer]. I have a fact, which contains activity for customers. These are the 3 entities that our calculations would be based on.

1
ssasmdx
didn't my answer help at all? – whytheq

1 Answers

0
votes

This is from an article by Chris Webb: http://cwebbbi.wordpress.com/2010/10/08/counting-new-and-returning-customers-in-mdx/

With a follow up article on how to optimize it: http://cwebbbi.wordpress.com/2013/06/28/optimising-returning-customers-calculations-in-mdx/

WITH
MEMBER MEASURES.[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
)
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]