Need help with Measure

[Anonymous]

Hello Community  -   The measure below works perfectly for what I am using it for...which is to discern the number of "new" customers.   This measure gives me a scalar value which essentially subtracts returning customers from existing and gives me the number of new customers.  

However, what I also need is the actual names of these customers (which is found in this field...[Bill To Customer_CustNum]).   

Currently, if I place the measure below in a table, and also place the customer_custnum field in that same table, even if I filter on a particular date (year) the resulting table list the correct number of new customers as the measure total, but it lists ALL of the customers in that period...not just the ones that are new.    So for example, in 2002 we had 442 customers so far in total...and 130 of them were new.    My table ends up showing all 442 customer names...and I only want it to show the names of the 130 that were new.    How do I need to modify this to do that?

New Bill to Customers = //calculating which customers within any particular month have purchased but haven't done so for the last X number of days

VAR _CustomersThisMonth = CALCULATETABLE(VALUES(SalesOrdersALL[Bill To Customer_CustNum]),USERELATIONSHIP('Dim_Date Table'[Date],SalesOrdersALL[Order Date]))

VAR _PriorCustomers = CALCULATETABLE(VALUES(SalesOrdersALL[Bill To Customer_CustNum]),USERELATIONSHIP('Dim_Date Table'[Date],SalesOrdersALL[Order Date]),ALL(SalesOrdersALL[Order Date Year]),ALL(SalesOrdersALL[Order Date Month]),ALL(SalesOrdersALL[Order Date Day]),

FILTER(ALL(SalesOrdersALL[Order Date]),

SalesOrdersALL[Order Date] > MIN(SalesOrdersALL[Order Date]) - [Churn Time Frame Value]

&& SalesOrdersALL[Order Date] < MIN(SalesOrdersALL[Order Date])))

VAR _NewCustomers = COUNTROWS(EXCEPT(_CustomersThisMonth,_PriorCustomers))

RETURN

_NewCustomers

[v-chenwuz-msft]

Hi @Anonymous ,

Maybe you can try use Dim_Date table to do the filter, let the range of dim_date between time period you want.

New Bill to customers =
VAR _CustomersThisMonth =
    CALCULATETABLE (
        VALUES ( SalesOrdersALL[Bill To Customer_CustNum] ),
        USERELATIONSHIP ( 'Dim_Date Table'[Date], SalesOrdersALL[Order Date] )
    )
VAR _PriorCustomers =
    CALCULATETABLE (
        VALUES ( SalesOrdersALL[Bill To Customer_CustNum] ),
        USERELATIONSHIP ( 'Dim_Date Table'[Date], SalesOrdersALL[Order Date] ),
        
        // 
        FILTER (
            ALL ( 'Dim_Date Table'[Date] ),
            [Date] // Time period you want
                > MIN ( SalesOrdersALL[Order Date] ) - [Churn Time Frame Value]
                && [Date] < MIN ( SalesOrdersALL[Order Date] )
        )
    ) //
    
VAR _NewCustomers =
    COUNTROWS ( EXCEPT ( _CustomersThisMonth, _PriorCustomers ) )
RETURN
    _NewCustomers

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.