Lost Customer Count Shows 3 Months Late

ScottTPA · January 19, 2020, 9:07pm

My customers are pretty simple, they bill on the first of each month. I am trying to count any customer that billed last month but did not in the current month as lost. I tried the formula from the video “Analyze Who Your Lost Customers Are”. Depending on how I tweak the dates, I either get no data or a correct count except the count is off by 3 months. In other words, I lost a customer in September but it counts as lost in December.
Is there a way to tweak the formula to make it do this? I appreciate any help you can offer.

Here is the dax I am using:
Lost Customers2 =
VAR Checkdate = 65
VAR Checkbackstartdate=120
VAR CustomersPurchased =
CALCULATETABLE (
VALUES ( Details[Customer] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Date]> MIN ( ‘Date’[Date]) - Checkbackstartdate
&& ‘Date’[MonthnYear]< MIN ( ‘Date’[MonthnYear] ) - Checkdate
)
)
VAR PriorCustomers =
CALCULATETABLE (
VALUES ( Details[Customer] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Date]> MIN ( ‘Date’[Date] ) -Checkdate
&& ‘Date’[Date] < MIN ( ‘Date’[Date] )
)
)
RETURN
COUNTROWS ( EXCEPT ( CustomersPurchased, PriorCustomers ) ) * -1

Melissa · January 19, 2020, 9:25pm

Question, why are you referring to Date’[MonthnYear] here and not the Date’[Date] ?

ScottTPA · January 19, 2020, 11:25pm

Thank you for replying. That was just me, trying anything to make it work.

Here’s the revised dax, it is behaving the same way. For example, I had a customer with its last bill was 9/1/19. The result shows it lost in December 2019:
Lost Customers2 =
VAR Checkdate = 65
VAR Checkbackstartdate=120
VAR CustomersPurchased =
CALCULATETABLE (
VALUES ( Details[Customer] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Date]> MIN ( ‘Date’[Date]) - Checkbackstartdate
&& ‘Date’[Date] <MIN ( ‘Date’[Date]) - Checkdate
)
)
VAR PriorCustomers =
CALCULATETABLE (
VALUES ( Details[Customer] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Date]> MIN ( ‘Date’[Date] ) -Checkdate
&& ‘Date’[Date] < MIN ( ‘Date’[Date] )
)
)
RETURN
COUNTROWS ( EXCEPT ( CustomersPurchased, PriorCustomers ) ) * -1

Melissa · January 20, 2020, 5:02am

Can you post a sample of your PBIX? That will make it easier to understand what is going on.

Thanks

ScottTPA · January 20, 2020, 12:13pm

FIle is working now, see later post.

ScottTPA · January 20, 2020, 10:02pm

Melissa, the file is attached.

Test Cancelled Customers.pbix (135.5 KB)

ScottTPA · January 20, 2020, 10:03pm

I got the file upload to work. See next post. It was not working this morning.

ScottTPA · January 21, 2020, 11:00am

I tried thinking about it a different way. All I am trying to do is compare the customer names in the current month, to the prior month. If the customer name was in the prior month, but not in the current month, it’s counted as Lost.
I tried this formula but my result is blank. When I test customerspurchased and priorcustomers I get the same count, so there is something wrong with how I am capturing the current any prior period counts.

I’d appreciate any suggestions on how to resolve this. My pbix is in an earlier reply in this thread:

Lost Customer 3 =

VAR CustomersPurchased =
CALCULATETABLE (
VALUES ( Details[Customer] ),
FILTER (‘Date’,‘Date’[Date]
))
VAR PriorCustomers =
CALCULATETABLE (
VALUES ( Details[Customer] ),
FILTER (‘Date’,DATEADD(‘Date’[Date],-1,MONTH)
))
RETURN
COUNTROWS ( EXCEPT ( CustomersPurchased, PriorCustomers ) )

Melissa · January 21, 2020, 4:10pm

So I think I understand a bit more now, but not quite there yet maybe @BrianJ can help out?

Looking at the last posting date for BWX, that’s in june (adding 120 days gives us a window with an upper limit of september 29th)
checkbackstartdate

And looking back from september that’s when BWX is first seen as ‘lost’
checkbackstartdate%20result

BrianJ · January 21, 2020, 6:57pm

@Melissa, @ScottTPA,

Glad to take a look. Will dig in after work and get back to you this evening.

Brian

ScottTPA · January 21, 2020, 8:03pm

Thank you.

nerminayoub1 · January 21, 2020, 9:23pm

did a trial my side …please check attached, hope it helpsTest Cancelled Customers_1.pbix (321.8 KB)

BrianJ · January 22, 2020, 12:34am

@nerminayoub1,

Love that treemap bar chart visual you used - great way to represent these results. Had not seen that one before.

Brian

ScottTPA · January 22, 2020, 1:59am

Thank you Brian and Melissa. This is very helpful. Is there a way to make it so it is not looking back 120 days. For example, in 2019, Advertising Agent’s name stops appearing after 4/1/2019. How can I show it as lost in May 2019? The 120 and 65 days were my attempt to tweak the formula but after I thought about it, I realized all I am trying to do is show:

Any customers on the list in the previous month, but not on the list in the current month are counted as lost in the current month;
Ideally, I’d like to also show any customers on the list in the current month, but not on the list in the prior month are WON.
is there a way to do this, ignoring any 120 day look-back, etc.
Sorry for the confusion, it took me awhile to think through this and simplify what I am trying to solve.

Melissa · January 22, 2020, 12:25pm

Hi Scott,

I’ve added a MonthOffset to the M code for the Date table and changed the Lost Customer measure:

Lost Customers = 
VAR ThisMonth = LOOKUPVALUE( 'Date'[MonthOffset], 'Date'[Year+Month], SELECTEDVALUE('Date'[Year+Month]))

VAR CustomersPurchased =
    CALCULATETABLE ( VALUES ( Details[Customer] ),
        FILTER ( ALL ( 'Date' ),
        'Date'[MonthOffset] = ThisMonth -1
        )
    )
VAR PriorCustomers =
    CALCULATETABLE ( VALUES ( Details[Customer] ),
        FILTER ( ALL ( 'Date' ),
        'Date'[MonthOffset] = ThisMonth
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( CustomersPurchased, PriorCustomers ) ) * -1

with this result:

.

For Gained Customers, the same logic is applied but the EXCEPT clause is switched, like below:

Gained Customers = 
VAR ThisMonth = LOOKUPVALUE( 'Date'[MonthOffset], 'Date'[Year+Month], SELECTEDVALUE('Date'[Year+Month]))

VAR CustomersPurchased =
    CALCULATETABLE ( VALUES ( Details[Customer] ),
        FILTER ( ALL ( 'Date' ),
        'Date'[MonthOffset] = ThisMonth -1
        )
    )
VAR PriorCustomers =
    CALCULATETABLE ( VALUES ( Details[Customer] ),
        FILTER ( ALL ( 'Date' ),
        'Date'[MonthOffset] = ThisMonth
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( PriorCustomers, CustomersPurchased ) )

With this result:

Here’s my file:
eDNA - Cancelled Customers.pbix (148.8 KB)

BrianJ · January 22, 2020, 2:29pm

@Melissa,

I really like the offset approach you use on these time intelligence-related solutions. I’ve always done it via variables, but I like how clean and simple the PQ offset makes the DAX code.

Somebody should make a video about this approach…

Brian

Melissa · January 22, 2020, 3:54pm

Now there’s an idea LOL

ScottTPA · January 23, 2020, 1:24am

Melissa, Thank you. This solution worked perfectly. Not only does it give me the lost customers, but it includes the new customers and current customers so I can do a roll-forward of their customer growth. I can’t thank you enough for this as I was really struggling to figure out a solution for this.

I will go back and review your solution in more detail so I can learn from it.

Brian and nerminayoub1, thank you. I will use your solutions also for this and future projects. Both of these are very helpful and I will review them in detail so I can learn from them. Thank you.

Melissa · January 23, 2020, 11:16am

Great, glad to hear that worked well for you.