Forum Discussion
Problem calculating retaining customers
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.
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]))
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.
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!
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?
