Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gauravnarchal
Post Prodigy
Post Prodigy

Lost clients with no purchase more than 6 months

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.
1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

 

View solution in original post

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

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
Memorable Member
Memorable Member

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 tradeABS(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
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.