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.