Forum Discussion
Pandadev
Post Prodigy
5 years agoFind Closest Matching Date based on matching ID code from 2 tables
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. Exam...
mahoneypat
Microsoft Employee
5 years agoHere is a measure expression that checks start dates in Table 2 before and after the Table 1 start date, and returns the closer one.
Closest Start =
VAR vThisDate =
MIN ( Table1[Start Date] )
VAR vMinAfter =
CALCULATE (
MIN ( Table2[Start_date] ),
Table2[Start_date] >= vThisDate
)
VAR vMaxBefore =
CALCULATE (
MAX ( Table2[Start_date] ),
Table2[Start_date] <= vThisDate
)
RETURN
IF (
DATEDIFF (
vThisDate,
vMinAfter,
DAY
)
< DATEDIFF (
vMaxBefore,
vThisDate,
DAY
),
vMinAfter,
vMaxBefore
)
Pat