Counting customers that have an order after completing a request form

[Sibrulotte]

Hi, 

I can't share a full model but a 5 line example of 3 tables should do the trick:

Customers:

Customer ID	Customer Name
1	John
2	Gary
3	Sally
4	Maude
5	Claude

Oders:

Order_ID	Customer ID	Order Date
CO-1	1	2023-12-01
CO-2	1	2024-01-15
CO-3	3	2024-01-25
CO-4	4	2023-10-01
CO-5	5	2024-02-14

Forms:

Form_id	Customer ID	Form Date
F-1	1	2024-01-01
F-2	2	2023-12-01
F-3	4	2024-01-01
F-4	4	2024-02-01
F-5	5	2023-12-01

I'm trying to count the number of customers that have orders that came in after a request was filed.

Result would be 

3 since only customer 1, 4 and 5 have orders coming after a request form.

Any answers help, thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column fomula in the Orders Table

Earliest form date = CALCULATE(MIN(Forms[Form Date]),FILTER(Forms,Forms[Customer ID]=EARLIER('Order'[Customer ID])))

Write this measure

Measure = CALCULATE(DISTINCTCOUNT('Order'[Customer ID]),FILTER('Order','Order'[Order Date]>'Order'[Earliest form date]&&'Order'[Earliest form date]<>BLANK()))

[Sibrulotte]

Thank you Anish, I see the goal and the path, but I somehow have a circular dependency from that column. 

My model is many to one from orders to customer and many to one from forms to customer.

[Ashish_Mathur]

You are welcome.  My solution does not require any relationships.  The ones that you have created are causing the error.  Please review them.

[Sibrulotte]

I in deed had to remove the relationshop to a single way...