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
Icon for Microsoft Employee rankMicrosoft 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,