topic Re: Count based on dates in two tables in DAX Commands and Tips

Count based on dates in two tables

zhris — Fri, 26 Jul 2024 21:17:36 GMT

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!

Re: Count based on dates in two tables

MattAllington — Fri, 26 Jul 2024 22:42:38 GMT

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

Re: Count based on dates in two tables

Gaga_Jin — Sat, 27 Jul 2024 10:13:39 GMT

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

Re: Count based on dates in two tables

Anonymous — Mon, 29 Jul 2024 02:40:31 GMT

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.