Count based on dates in two tables

[zhris]

Hi!

 I have two tables:

• One has an order ID and a submission date
• The other has a Completion date.

I'm trying to write a DAX measure to count orders where the submission date is on or before the completion date, but I'm running into an error in my syntax. Here's what I'm trying so far:

orders pre = 
CALCULATE(
    DISTINCTCOUNT(Table1[JOB_ID])
       , Table2[Completion Date] >= Table1[JOB_CREATE_DATE]

Is this possible? Thanks!

[Anonymous · Accepted solution]

Hi, @zhris 

You can try the following methods.

Sample data:

orders pre = 
CALCULATE(
DISTINCTCOUNT(Completion[Order ID]),FILTER(Completion,[Completion Date]>=MAX(Submission[Submission date])))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

[Gaga_Jin]

CALCULATE(
COUNTROWS('Orders'),
FILTER(
'Orders',
'Orders'[SubmissionDate] <=
RELATED('Completions'[CompletionDate])
)
)

[MattAllington]

You need to share details on the tables, the relationships between them and which columns are part of the relationship .