Find Closest Matching Date based on matching ID code from 2 tables

Question

I am trying to compare the start and end events from 2 tables , both tables are joined by a matching ID , I am trying to find the closest matching date in table table 2 for each row in table 1.Example below would match ID 01 Table&nbsp;15/12/2020&nbsp; &nbsp;26/12/2020 withTable 2 -&nbsp;13/12/2020&nbsp; &nbsp;25/12/2020&nbsp;as that is closest matchTable 1ID&nbsp; &nbsp; &nbsp;Start Date&nbsp; &nbsp; &nbsp; End Date01&nbsp; &nbsp; &nbsp;15/12/2020&nbsp; &nbsp;26/12/202002&nbsp; &nbsp; &nbsp;09/12/2020&nbsp; &nbsp;18/12/2020&nbsp;Table 2ID&nbsp; &nbsp; &nbsp;Start_Date&nbsp; &nbsp; &nbsp; End_Date01&nbsp; &nbsp; &nbsp;15/06/2020&nbsp; &nbsp;22/06/202001&nbsp; &nbsp; &nbsp;15/08/2020&nbsp; &nbsp;11/07/202001&nbsp; &nbsp; &nbsp;15/07/2020&nbsp; &nbsp;10/08/202001&nbsp; &nbsp; &nbsp;15/10/2020&nbsp; &nbsp;11/11/202001&nbsp; &nbsp; &nbsp;13/12/2020&nbsp; &nbsp;25/12/202001&nbsp; &nbsp; &nbsp;29/12/2020&nbsp;&nbsp; 05/01/202102&nbsp; &nbsp; &nbsp;09/12/2020&nbsp; &nbsp;18/12/2020

v-lionel-msft · Answer

Hi&nbsp;Pandadev&nbsp;,
&nbsp;
Please refer to my .pbix file.

&nbsp;
Best regards,Lionel Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

pandadev · Answer

Hi Thanks for looking at this , is it possible to adapt this to just check the start date , as I forgot that one table does not have an end date in it , thanks

mahoneypat · Answer

Here is a measure expression that checks start dates in Table 2 before and after the Table 1 start date, and returns the closer one.
&nbsp;
Closest&nbsp;Start&nbsp;=VAR&nbsp;vThisDate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;Table1[Start&nbsp;Date]&nbsp;)VAR&nbsp;vMinAfter&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;Table2[Start_date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table2[Start_date]&nbsp;&gt;=&nbsp;vThisDate&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;vMaxBefore&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;Table2[Start_date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table2[Start_date]&nbsp;&lt;=&nbsp;vThisDate&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATEDIFF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vThisDate,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vMinAfter,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DAY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;&nbsp;DATEDIFF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vMaxBefore,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vThisDate,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DAY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vMinAfter,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vMaxBefore&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
Pat
&nbsp;

pandadev · Answer

Thanks , Pat , is it possible to include the ID in the measure , as I need the closest matching date based on ID

pandadev · Answer

Hi I added your measures from the attached pbix , but for some reason it is not working correctly , my ID codes are a range of number both negative and positive values , like 346722 and -1381 , would this be why it is not working as in the example the id codes were 1 and 2 .

Forum Discussion

Find Closest Matching Date based on matching ID code from 2 tables

6 Replies

Related content

Matching Column Values

Event Schema Inside Eventstream Never Matching

totals dont match

List.Remove (Matching) Items

Fuzzy matching