Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lost Customer

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 20191
Mar 20195
Apr 20190
May 20192
June 20193
 

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

 

 

Thank you!

  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 
      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.