Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have file which shows how many shares each customer owns by month (each column represents a customer). I want to identify lost customers (the second colum the customer is lost in Jan 2021)
Solved! Go to Solution.
Hi @Absalon29 ,
This is my test table:
Please create a new column:
Column 2 =
VAR _Last =
MAXX (
FILTER (
'Table1',
'Table1'[Name] = EARLIER ( Table1[Name] )
&& 'Table1'[Date]
= MAXX (
FILTER ( 'Table1', 'Table1'[Name] = EARLIER ( Table1[Name] ) ),
'Table1'[Date]
)
),
'Table1'[Value]
)
VAR _First =
MAXX (
FILTER (
'Table1',
'Table1'[Name] = EARLIER ( Table1[Name] )
&& 'Table1'[Date]
= MINX (
FILTER ( 'Table1', 'Table1'[Name] = EARLIER ( Table1[Name] ) ),
'Table1'[Date]
)
),
'Table1'[Value]
)
RETURN
IF ( _First = BLANK (), "New client", IF ( _Last = BLANK (), "Lost client" ) )
I think this is result you want:
In the screenshot, client B is identified as lost client and client C is identified as new client.
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks very much that was a great help!
Hi @Absalon29 ,
Do you want to highlight lost customers in Jan 2021 ? If it is like this, pleae try following steps.
This is my test table:
Create two measures:
Measure1 = IF(YEAR(MAX('Table'[Date])) = 2021 && MONTH(MAX('Table'[Date])) = 1 && MAX('Table'[Customer B]) = BLANK(),"red")
Measure2 = IF(YEAR(MAX('Table'[Date])) = 2021 && MONTH(MAX('Table'[Date])) = 1 && MAX('Table'[Customer D]) = BLANK(),"red")
Conditional formatting:
Is this the result you want:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, apologies but I wasn't clear enough. In my attached screenshot each month shows the client's balance of shares at the end of each month. To take the example of the second column the balance falls from 4171.00 in Oct 2020 to 40 in Jan 2021. In Feb 2021 the value is blank, because the client has withdrawn their investment. We want to count this as a "Lost client". In the third column in June 2022 a new client adds 10.18 shares, because May 2022 is blank and June 2022 has a value, we want to count this clients as a "New client". So I been trying to write a measure which counts the new and lost clients based on this logic.
Hi @Absalon29 ,
This is my test table:
Please create a new column:
Column 2 =
VAR _Last =
MAXX (
FILTER (
'Table1',
'Table1'[Name] = EARLIER ( Table1[Name] )
&& 'Table1'[Date]
= MAXX (
FILTER ( 'Table1', 'Table1'[Name] = EARLIER ( Table1[Name] ) ),
'Table1'[Date]
)
),
'Table1'[Value]
)
VAR _First =
MAXX (
FILTER (
'Table1',
'Table1'[Name] = EARLIER ( Table1[Name] )
&& 'Table1'[Date]
= MINX (
FILTER ( 'Table1', 'Table1'[Name] = EARLIER ( Table1[Name] ) ),
'Table1'[Date]
)
),
'Table1'[Value]
)
RETURN
IF ( _First = BLANK (), "New client", IF ( _Last = BLANK (), "Lost client" ) )
I think this is result you want:
In the screenshot, client B is identified as lost client and client C is identified as new client.
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523