Forum Discussion
Problem calculating retaining customers
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:
And Here is the sample data and calculation I have
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
9 Replies
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.
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.
Hi v-diye-msft and parry2k
Here is the sample data and calculation I have
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
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]))
