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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

New and lost customers

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)

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Helper III

Thanks very much that was a great help!

Community Support

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.

Helper III

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.

Community Support

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.

Super User

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

Helpful resources

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors