Forum Discussion
Find Closest Matching Date based on matching ID code from 2 tables
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.
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
- Pandadev5 years agoPost Prodigy
Thanks , Pat , is it possible to include the ID in the measure , as I need the closest matching date based on ID