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.

Reply
Absalon29
Helper III
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)

 

 

 

Absalon29_0-1669390419435.png

 

1 ACCEPTED SOLUTION

Hi @Absalon29 ,

 

This is my test table:

vyadongfmsft_0-1669700755522.png

 

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:

vyadongfmsft_1-1669700906762.png             vyadongfmsft_2-1669700921273.png

 

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.

View solution in original post

5 REPLIES 5
Absalon29
Helper III
Helper III

Thanks very much that was a great help!

v-yadongf-msft
Community Support
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:

vyadongfmsft_0-1669619042374.png

 

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:

vyadongfmsft_1-1669619233801.png

 

vyadongfmsft_2-1669619288622.png

 

Is this the result you want:

vyadongfmsft_3-1669619312119.png

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:

vyadongfmsft_0-1669700755522.png

 

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:

vyadongfmsft_1-1669700906762.png             vyadongfmsft_2-1669700921273.png

 

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
Super User
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.