Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need help with Measure

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
Need Help

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.