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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Select New/ Lost/ Returning/ Recovered Customers in filter

Hi all,

 

I'm doing a Customer base analysis in Power BI and have succesfully calculated New/ Lost/ Returning or Recovered Customers by using measures. So, now I can filter and see the no. of customers for each selected period. But, I also want to select them, not only calculate. E.g. select Lost customers to set up an activation campaign for those specifcially. 

 

This is the calculation for determining Lost Customers, which is working correctly:

 

 

# Lost Customers = 
VAR LastDateLost =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        ALLSELECTED ( 'Date' )
    )
VAR CustomersWithLostDate =
    CALCULATETABLE (                        -- Prepares a table that 
        ADDCOLUMNS (                        -- for each customer contains 
            VALUES ( Sales[CustomerKey] ),  -- the date when they are considered lost 
            "@LostCustomerDate", [Date Lost Customer]
        ),
        ALLEXCEPT ( Sales, Customer ),
        'Date'[Date] <= LastDateLost
    )
VAR LostCustomers =
    FILTER (                               
        CustomersWithLostDate,          -- Filters the customers
        [@LostCustomerDate]             -- whose lost customer date
            IN VALUES ( 'Date'[Date] )  -- fall within the current period
    )
VAR Result =
    COUNTROWS ( LostCustomers )         -- The count of the lost customers does not
                                        -- use the Sales table (no sales in the period)
RETURN
    Result

 

 


This only allows me to calculate the number and I'm not able to select those who are included in the calculation. Any idea how to do this? Don't have to stick to the calculation example I included above, if there's a better or more efficient option to set this up!

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Based on your description, not certain what is your expected output. Could you please consider sharing a sample file without any sesentive information or any screenshots about this issue for further discussion?

 

Sample data and expected output would help tremendously.
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

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , do you need slicer only customer name?

 

refer if my blogs can help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

 

Anonymous
Not applicable

In ideal situation, I would like to have a filter option so I can select type of Customer (New, Lost etc) in combination with a date selection filter. Also in the blog you're refering to It's calculating the customer, but doesn't allow you to select them and only get an overview of the lost customers. Is it clear what I'm looking for?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.