Forum Discussion
clarkpaul
Helper I
Helper IJoining 2 tables based on a Date Range
Drivers deliver a machine and then call us to update the status. (Delivery Complete, Incomplete, Damaged, etc.) The delivery date is from one table and the debrief date is from another table.
The debrief date could be the same date OR may be a couple of days later. I create a join key based on the serial number and the date.
I cannot use just the serial since the machine may be a short term rental and it could show up in the tables several times.
Is there a way to join the tables based on a date range?
Hi clarkpaul,
One of the options is to create a calculated column like this:
In plain text:
Debrief Date Calculated = VAR currentDate = [Delivery Date] VAR currentSN = [Serial Number] RETURN MINX ( FILTER ( 'Table B', [Serial Number] = currentSN && [Debrief Date] >= currentDate ), [Debrief Date] )Best Regards,
Alexander
1 Reply
- barritown
Solution Sage
Hi clarkpaul,
One of the options is to create a calculated column like this:
In plain text:
Debrief Date Calculated = VAR currentDate = [Delivery Date] VAR currentSN = [Serial Number] RETURN MINX ( FILTER ( 'Table B', [Serial Number] = currentSN && [Debrief Date] >= currentDate ), [Debrief Date] )Best Regards,
Alexander