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