Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to 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
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
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
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |