Lost clients with no purchase more than 6 months

[gauravnarchal]

Dear All,  I have created the below measure in Power Bi but I am not getting the results for NBTO, New Client & Loss clients. I had already created the measure for month difference which is working correctly.

Basically I need the status of each client:-

Any client trading for more than 12 months is "Existing Client"

Any client trading for less than or (=) 12 months is "NBTO"

Any client trading for 0 months is "New Client"

Any client NOT traded from more than 6 months from the today date is "Lost Client"

GN Customer Status =

IF([GN Months Difference]>12,"Existing Client",

IF([GN Months Difference]<=12,"NBTO",

IF([GN Months Difference]=0,"New Client",

IF([GN Last Sales Date]-TODAY() >6,"Loss Client"

)

)

)

)

Appreciate your help and thank you in advance.

[v-deddai1-msft · Accepted solution]

Hi @gauravnarchal ,

What is in your [GN Months Difference]? In the absence of details, I just find a problem with the last part of your formula：

GN Customer Status =
IF (
    [GN Months Difference] > 12,
    "Existing Client",
    IF (
        [GN Months Difference] <= 12,
        "NBTO",
        IF (
            [GN Months Difference] = 0,
            "New Client",
            IF ( DATEDIFF([GN Last Sales Date],Today(),Month) > 6, "Loss Client" )
        )
    )
)

Best Regards,

Dedmon Dai

[nandic]

Hi @gauravnarchal ,

The order of if statements is vital in this case. You should create additional columns which will help you build criteria.
Examples:
Months before last trade: ABS(DATEDIFF(TODAY(),Trades[Last trade per client],MONTH))

First trade month =
IF (
    DATEDIFF (
        TODAY (),
        CALCULATE (
            MIN ( Trades[Trade Date] ),
            ALLEXCEPT ( Trades, Trades[Client Id] )
        ),
        MONTH
    ) = 0,
    "New customer",
    "Old customer"
)

You should also build criteria for all options and make sure you have all options covered. After that, create a column as you started, but with proper order.

Cheers,

Nemanja

[Greg_Deckler]

@gauravnarchal - Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

But, this might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13