Lost Customers Calculation


#1

Hi Sam
I’m a relative beginner with DAX, but I’m trying to develop a Dashboard quickly.
I watched your Scenario Method Workshops going through the New Customer and Lost Customer analysis. However, the Lost Customer DAX function wasn’t exactly what I was looking for…

I wrote this DAX function for Lost Customers and it seems to work - well sort of.
The problem is that all the data points are time-shifted by 1 year!
So when I verify the customer data for a lost customer on Jan-2018, I find the customer was lost on Jan-2017.

I don’t really understand the operation of the Filter function on the Date table.
Could you point out some videos that go through the operation?
Thanks,
Rick

LostCustomers = 
VAR CurrentCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MAX (Date4[Date]) -365   &&
                            Date4[Date] < MAX (Date4[Date])))
                                                                
VAR PriorCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MIN (Date4[Date]) - 365  &&
                            Date4[Date] < MIN(Date4[Date])-90))
                            
RETURN
COUNTROWS(EXCEPT(PriorCustomers, CurrentCustomers))

#2

How you judge who a lost customer is can vary a little bit, so deciding that logic is the first thing to do.

What the particular formula you reference is doing is…

Looking backwards and comparing the customers who bought in a time window or 12 months to 3 months ago. Then comparing that to customers who purchased in the last 3 months.

If a customer did purchase in the 9 month window but didn’t in the last 3 months, then they are considered lost.

That clarify things?

This is a recent release

Check this out. I cover it in detail during this one.


#3

Thanks for your response.
It looks like I’m on the right track…
What is really bothering me with this function, is that I’m expecting the data to line-up with the current time context but its actually one year behind…
So, its indicating 4 Lost Customers in Jan 2018, but they were lost in Jan 2017.
Everything is great, except the timeline is off by 1 year!

This is where I’m getting confused and don’t understand what is going on.
Could you elaborate what the function is doing a bit more?

This is the other DAX function I’m using as well.

LostCustomerSales = 
VAR CurrentCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MAX (Date4[Date]) -365   &&
                            Date4[Date] < MAX (Date4[Date])))
                                                                
VAR PriorCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MIN (Date4[Date]) - 365  &&
                            Date4[Date] < MIN(Date4[Date])-90))
                            
RETURN
Calculate([TotalSales], 
    DATESBETWEEN( Date4[Date], MIN( Date4[Date]) - 365, MIN( Date4[Date] ) - 90 )  ,
    EXCEPT(PriorCustomers, CurrentCustomers))

#4

Can we just double check the formula here your using.

The way it is currently written is sounds like this…

(I’m presuming you’re showing this at a monthly context - Can you add an image of the results you’re seeing also?)

…In the current monthly context you are comparing all customers who have bought in the last year to a customer list who have purchase from 12 to 3 months ago.

There’s to much overlap here of the list and it doesn’t seem right to me.

Can you write out the logic of what you consider a lost customer?

Then also if you can add the file here as well then I can actually see where it’s going wrong.

Thanks


#5

I watched the video you recommend a few more times and it started to make sense!
I finally understand the Filter / Min/Max function when applied to Date table.
For the first time it really clicked!

My problem was with the CurrentCustomer time period: -365 to -60 days.
There were too many New Customers and Lost Customers during this period that are not accounted for in the PriorCustomer time period: -60 to 0 days.

I settled for the following time periods:
CurrentCustomers: -90 to -60 days
PriorCustomers: -60 to 0 days

Below is my final DAX function that works for my application.
I also illustrate the difference using the 2 time periods.
Its quite large!

LostCustomers = 
VAR CurrentCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MIN (Date4[Date]) - 90   &&
                            Date4[Date] < MIN (Date4[Date]) - 60))
                                                                
VAR PriorCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MIN (Date4[Date]) - 60  &&
                            Date4[Date] < MAX(Date4[Date])))
                            
VAR CurrentMonthCustomers = CALCULATETABLE(VALUES(CustomerInvoices8[Customer]),
                        FILTER(All(Date4),
                            Date4[Date] > MIN (Date4[Date])  &&
                            Date4[Date] < MAX(Date4[Date])))                        
                            
RETURN
IF (COUNTROWS(CurrentMonthCustomers) > 0,
        COUNTROWS(EXCEPT(CurrentCustomers, PriorCustomers)) * -1,
        0)


#6

Ok great. Just to confirm you have it right now?

Looks good though. The logic seems more logical to me.

A really key concept/function to understand on this one is CALCULATETABLE. It’s actually a fascinating function that enable so many advanced insights just like this one.

Hopefully you’re getting the hang of it a bit more.

Here’s a video diving into CALCULATETABLE


#7

Hi Sam,
The Churn Analytics solution is great!
I found that the fixed period of 60 days for certain customers did not match up with the apparent churning pattern (Freight Forwarding with irregular yet steady customers).

Hence I have added a “select # days” table to allow the user to run different scenarios.

Paul


#8

Hi Sam
Yes, I understand this function and what its doing now.
Which is great because it looks like the time period must be adapted to the customer data your using.
We’re a professional services business and most of our invoices are sent out on the last day of each month and we have a small churn of new & lost clients every month.

So it seems like its best to look back only 1 month beyond the churn-period.
i.e. CurrentCustomers: -90 to -60 days
PriorCustomers: -60 to 0 days

I will test the results some more, but it looks good so far.
Thanks for your help.
Rick