Measure using CALCULATE, FILTER and USERELATIONSHIP

[Anonymous]

Hello Community  -  The measure below works but with one important problem:   it is referencing the wrong date.  

In my salesorders table, we normally use "due date" and that is our primary connection to the date table.   I have an inactive relationship to the date table with "order date"  and that is the date I need to incorporate below.   I've tried placing USERELATIONSHIP into the measure but with no luck.    

We of course want to base our new customers on the date they placed an order, not the due date of the order.   And that is what this measure needs to do.  

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

VAR _CustomersThisMonth = VALUES(SalesOrdersALL[CustNum_Cust_Seq_Orders])

VAR _PriorCustomers = CALCULATETABLE(VALUES(SalesOrdersALL[CustNum_Cust_Seq_Orders]),

FILTER(ALL('Dim_Date Table'),

'Dim_Date Table'[Date] > MIN('Dim_Date Table'[Date]) - [Churn Time Frame Value] &&

'Dim_Date Table'[Date] < MIN('Dim_Date Table'[Date])))

RETURN

COUNTROWS(EXCEPT(_CustomersThisMonth,_PriorCustomers))

[julie_13579]

Formula works perfectly! Do you also have a formula for lost clients? 

[lbendlin]

RETURN

COUNTROWS(EXCEPT(_PriorCustomers,_CustomersThisMonth))

[lbendlin · Accepted solution]

You need to use CALCULATETABLE and USERELATIONSHIP for both your _CustomersThisMonth and _PriorCustomers variables.