Lost Customer

[Anonymous]

Hello, 

I need assistance in calculating the number of lost customers in a given month.

Lost Customer = No revenue for the past 3 months and in the future.

I am currently using two fields, date and revenue.

I've tried to follow instructions posted on multiple sites including this one, https://www.daxpatterns.com/new-and-returning-customers/ , but none of them seems to work well.

This is the example of my lost calculation formula.

My goal is to show a number of lost customers in any given month. For example:

Month	# of lost customers
Jan 2019	3
Feb 2019	1
Mar 2019	5
Apr 2019	0
May 2019	2
June 2019	3

I'd truly appreciated it if anyone can help me with this.

Thank you!

[mahoneypat · Accepted solution]

Please try an expression like this:

Lost Customers =
VAR __3mosago =
    EDATE ( MIN ( 'Date'[Date] ), -3 )
VAR __4mosago =
    EDATE ( MIN ( 'Date'[Date] ), -4 )
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( REVENUE[customer_id] ),
            AND (
                ISBLANK (
                    CALCULATE ( COUNTROWS ( Revenue ), ALL ( 'Date' ), 'Date'[Date] >= __3mosago )
                ),
                NOT (
                    ISBLANK (
                        CALCULATE ( COUNTROWS ( Revenue ), ALL ( 'Date' ), 'Date'[Date] >= __4mosago )
                    )
                )
            )
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat 

This works perfectly. Thank you very much.

I understand that this formula is looking at the customers with revenue 4 months ago, and no rev for the past 3 months. However, how is this formula able to look ahead in the time to see whether the customers have any revenue or not? If you can point that out in the formula, I'd truly appreciated it. I am just trying to understand and learn this.

Thank you,

[mahoneypat]

Yes.  It grabs the Min date from the current context in the visual (e.g., Jan 1 for January month, Feb 1 for February), and then looks backward (3 and 4 months), but also forward (both have >= in them).  Also, each lost customer should only get counted in the first month that satisfies the condition (not blank 4 mos ago, but blank for last 3 months), and won't get counted again each following month (as they would fail the 4 month condition).

Regards,

Pat

[harshnathani]

HI @Anonymous ,

Have a look at these blogs.

https://radacad.com/customer-retention-in-power-bi-dax-measures

https://radacad.com/lost-customers-dax-calculation-for-power-bi

https://blog.enterprisedna.co/analyze-who-your-lost-customers-are-using-power-bi-dax/

https://blog.crossjoin.co.uk/2010/05/03/counting-returning-customers-in-dax/

Regards,

Harsh Nathani

Regards,

[Anonymous]

@harshnathani 
Thank you for the links. I've actually visited all of those before, and I am still not getting the number that I want. I know I am missing something.

https://blog.enterprisedna.co/analyze-who-your-lost-customers-are-using-power-bi-dax/ 

I've created DAX based on the above link, but for some reason, for any given month, the number is showing for the month prior.

The Aug 2019 amount shown on the above chart is actually for Jul 2019.

My formula is this one below.