Latest Enterprise DNA Initiatives


Lost Customer Count Shows 3 Months Late

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

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

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

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

Thanks

1 Like

FIle is working now, see later post.

Melissa, the file is attached.

Test Cancelled Customers.pbix (135.5 KB)

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

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 ) )

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

@Melissa, @ScottTPA,

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

  • Brian
1 Like

Thank you.

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

3 Likes

@nerminayoub1,

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

  • Brian

1 Like

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:

  1. 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;
  2. 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.

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:
image

.

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)

3 Likes

@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…:wink:

  • Brian
1 Like

Now there’s an idea :bulb: LOL

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.

Great, glad to hear that worked well for you.