Problem calculating retaining customers

[Anonymous]

Could anybody please explain why it doesn't work when I'm trying to identify if a new customer is retained. The logic I'm having is that if a customer have never made any purchase before current purchase, and will make a purchase in next 30 days, then this new customer is retained and will be marked as a retaining customer.

The DAX function looks like this:

Retaining Customers =

COUNTROWS (

FILTER (

ADDCOLUMNS (

VALUES ( Orders[会员ID] ),

"PreviousSales", CALCULATE (

COUNTROWS ( Orders ),

FILTER (

ALL( 'Dates' ),

'Dates'[Date] < MIN ( 'Dates'[Date] )

)

),

"SalesIn30Days", CALCULATE(

COUNTROWS ( Orders ),

FILTER (

ALL( 'Dates' ),

'Dates'[Date] < ( MIN ('Dates'[Date]) + 30) && 'Dates'[Date] > MIN('Dates'[Date]) )

)

),

[PreviousSales] = 0 && [SalesIn30Days] > 0

)

)

The previousSales part works fine - it correctly identifies all new customers, but the second part gives the wrong answer. It always returns all customers.

Can anybody help?

UPDATED：

The sample table looks like this

And Here is the sample data and calculation I have

Sample Data and Calculation 

I'm hoping to have retaining customers = 2 for January because customer 2 and 3 are new customers and make another purchase within 30 days of their first purchase. Customer 1 is not a new customer for January so I won't be counted and customer 4 doesn't make any purchase after first purchase, so it won't be counted as well.

But my calculation gives me retaining customers = 3 and it seems to be problem with customer 4

[amitchandak]

@Anonymous , You can two new columns like this that can help you to get your calculations, these are in the order table

First purchase = minx(filter(Order,[CustomerID] = earlier([CustomerID])),[Order Date])
last purchase = maxx(filter(Order,[CustomerID] = earlier([CustomerID]) && [Order Date] < earlier([Order Date])),[Order Date])

[Anonymous]

@amitchandak Thank you for replying!

The dataset is really large so using ealier function is a bit slow, that's why I'm trying to use measure to calculate this number instead of adding calculated columns.

Another reason I want to use a measure is that I need to drill down through different levels, so it's impossible to do this by adding calculated columns.

[amitchandak]

@Anonymous , Try like this.  But use dimensions now

First purchase = minx(filter(all(Customer),Customer[CustomerID] = max(Customer[CustomerID])),[Order Date])

last purchase = calculate(maxx(filter(all(Customer),[CustomerID] = max(Customer[CustomerID])),[Order Date]),,filter( all(Date),Date[Date] < Max(Date[Date]))

[Anonymous]

@amitchandak Thank you so much!

The thing is that I only care about first and second purchase instead of last purchase.

If a customer is new (make first purchase) and then this same customer make another purchase within 30 days of his first purchase, this customer will be considered as a retaining customer. This customer might continues making further purchases but those won't be considered when calculating number of retaining customers.

Actually I already have calculated columns added, and using calculated columns give me the correct answer. But like what I've mentioned ealier, I want to use measures so I can drill down through the data when doing dashboard and report.

[parry2k]

@Anonymous here are the measures which can get you first purchase date and the very next purchase date after first purchase and you can take it from there

First Purchase = CALCULATE ( MIN ( Orders[Date] ), ALLEXCEPT ( Orders, Orders[customer ID] ) )

Next Purchase = 
VAR __firsPurchase = [First Purchase] 
RETURN CALCULATE ( MIN ( Orders[Date] ), ALLEXCEPT ( ORders, Orders[customer ID] ), Dates[Date] > __firsPurchase )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Thank you @amitchandak

Now I have

Retaining Customers = 
COUNTROWS(
    FILTER(
        VALUES(Orders[会员ID]),
        CALCULATE(
            COUNTROWS(Orders),
            FILTER(
                ALLEXCEPT(Orders, Orders[会员ID]),
                DATEDIFF([First Order Date], [Second Order Date],DAY) <= [Churn Time Value]
            )
        )
    )
) 

This gives number of retaining customers. But if a customer keep making purchases in different months, this customer will be counted as retaining customer for every month he places orders.

How am I suppose to change my function so a customer will only be counted once in his first purchase month?

[Anonymous]

Hi @v-diye-msft and @parry2k 

Here is the sample data and calculation I have

Sample Data and Calculation 

I'm hoping to have retaining customers = 2 for January because customer 2 and 3 are new customers and make another purchase within 30 days of their first purchase. Customer 1 is not a new customer for January so I won't be counted and customer 4 doesn't make any purchase after first purchase, so it won't be counted as well.

But my calculation gives me retaining customers = 3 and it seems to be problem with customer 4

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[parry2k]

@Anonymous too many calculations going on, it will be easier if you put these calculations in separate measures and identify what is not working and what should be the expected result. It will help to debug and then if you want, you can combine all that in one, also share sample data, relationship and expected output.