Identifying loss customer after 4 month no purchase

[Anonymous]

Hi,

I would like to create a measure or new column that identifies a lost customer. 

A lost customer is defined as a customer that haven't made a purchase for 4 months.

If after 4 months the customer makes a purchase again it is not a lost customer anymore.

I have had a simular topic regarding new customers, which can be found here

I am using the follwing data:

(Last column in red is the desired column)

Company	Company Nr	Product	Purchase date	Value	Loss customer
Company A	1111	Soda	12-1-2018	100	Loss
Company A	1111	Soda	24-2-2018	70	Loss
Company A	1111	Beer	8-3-2018	145	Loss
Company A	1111	Water	12-3-2018	293	Loss
Company B	223311	Beer	12-5-2017	80
Company B	223311	Beer	13-6-2017	200
Company B	223311	Beer	24-6-2017	40
Company B	223311	Water	24-5-2018	30
Company B	223311	Soda	12-6-2018	60
Company B	223311	Soda	31-7-2018	50
Company B	223311	Beer	2-8-2018	400
Company C	33229153	Soda	22-2-2017	23	Loss
Company C	33229153	Beer	25-5-2017	51	Loss
Company C	33229153	Soda	12-8-2017	175	Loss
Company C	33229153	Water	23-11-2017	46	Loss
Company C	33229153	Soda	7-1-2018	34	Loss
Company C	33229153	Beer	16-2-2018	25	Loss

Furtheremore I have to be able to include or exclude customers manually.

Like the DAX for new customers has as well:

New Customer =
VAR CurrentYear = 2017
VAR PriorYear = CurrentYear - 1
VAR FirstPurchaseCurrentYear =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Company] ),
            OR (
                YEAR ( Table1[Date] ) = CurrentYear,
                AND ( YEAR ( Table1[Date] ) = PriorYear, MONTH ( Table1[Date] ) = 12 )
            )
        )
    )
VAR LastPurchasebeforeCurrentYear =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Company] ),
            Table1[Date] < DATE ( PriorYear, 12, 1 )
        )
    )
VAR SalesGap =
    DATEDIFF ( LastPurchasebeforeCurrentYear, FirstPurchaseCurrentYear, MONTH )
RETURN
    IF (
        AND (
            SalesGap > 12
                || SalesGap = BLANK (),
            OR (
                YEAR ( Table1[Date] ) = CurrentYear,
                AND ( YEAR ( Table1[Date] ) = PriorYear, MONTH ( Table1[Date] ) = 12 )
            )
        ),
        "New Customer"
    )

Hope you can help me.

If any questions please do not hesitate to ask!

[v-jiascu-msft]

Hi @Anonymous,

Could you mark the proper answer as a solution please?

Best Regards,

Dale

[edhans · Accepted solution]

See if this will help get you on the right track.

First, I calculated the maximum purchase date for each customer:

Max Sales Date = 
CALCULATE(
    MAX(Sales[Purchase date]),
    ALLEXCEPT(Sales,Sales[Company])
)

Then I calculated the duration in months from today's date to [Max Sales Date] above:

Duration = 
DATEDIFF(
    [Max Sales Date],
    TODAY(),
    MONTH
)

Then I made a simple IF() comparison:

Lost Customer = 
IF(
    [Duration] > 4,
    "Lost",
    BLANK()
)

That gave me a table that looks like this:

You can hide the intermediate measures, or combine them into one big complex measure if desired.

See this file.

[MAAbdullah_47]

Hi @edhans  can It works as column not measure? 

[edhans]

Not as written, but why would you want to? In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns