New and lost customers

[Absalon29]

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)

[Absalon29]

Thanks very much that was a great help!

[v-yadongf-msft]

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.

[Absalon29]

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. 

[v-yadongf-msft · Accepted 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.

[lbendlin]

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