CODEWITHSUNDEEP
ssasmdx
Mez
Mez

Reputation: 4726

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

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?

Upvotes: 0

Views: 259

Answers (1)

FrankPl
FrankPl

Reputation: 13315

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.

Upvotes: 2

Related Questions