Forum Discussion
Count based on dates in two tables
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!
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.
3 Replies
You need to share details on the tables, the relationships between them and which columns are part of the relationship .
CALCULATE(
COUNTROWS('Orders'),
FILTER(
'Orders',
'Orders'[SubmissionDate] <=
RELATED('Completions'[CompletionDate])
)
)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.
