Calculate customers who have generated revenue for the first time in MDX

Question

I would like to calculate customers, who have been active the first time ever in the current period. Let's say the calculation for active customer is the following

MEMBER [Measures].[ActiveCustomers] AS
 Sum(
   [Customer].[Customer Id].Members, 
   IIF([Measures].[Revenue] > 0,1,NULL))

How, or what functions do I need to use to determine the customers whom have generated revenue the first time ever, in the current period?

How do you determine the current period? Do you want to use the last member of the bottom level of your time hierarchy? Or do you want to calculate something from the current system date? Or do you mean the CurrentMember of this hierarchy? — FrankPl
I will pivot this against a date hierarchy, let's say it's named [Calendar].[Year Month Day], and I will use the [Calendar].[Year Month Day].CurrentMember to determine the current period. — Mez

FrankPl FrankPl · Accepted Answer · 2014-07-08T16:42:26

If, when you write "to calculate customers", you mean "to get the list of customer ids", then

Filter([Customer].[Customer Id].[Customer Id].Members,
       ([Calendar].[Year Month Day].CurrentMember, [Measures].[Revenue]) > 0
       AND
       Sum(null : [Calendar].[Year Month Day].CurrentMember.PrevMember, [Measures].[Revenue]) = 0
      )

should deliver that. This checks that in the current period, there is revenue > 0, and the sum across all preceding periods is zero. This may fail if you have negative revenues, as then the positive and negative revenues could cancel each other, but I am assuming this is not the case here.

Calculate customers who have generated revenue for the first time in MDX

1 Answers