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, d...
  • 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

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