Power Pivot - Lookup based on Last Closest Date/Time

Afternoon All,

I am having a hell of a time trying to relate some data from a few systems. They have very little in common, really just an agent name and date/Time.

The Date time is stamped at different times due to process and varying systems.

What I am looking to do is take a record that has a time that a user will have generated when submitting notes in one system. I want to tie that back to the call record that comes closest in time to the time in which that record was submitted.

This file is Millions of records so not realistic to post.
The User ID Number will match 100%, the Date Time Format MM/DD/YYYY HH:MM:SS will not ever be exact but needs to be the deciding factor on which record to base its lookup off of. Being that it is relative to that date it needs to be a lookup that operates very similar to what a match would do to get the greatest value for date/time but not greater than the one on the table operating the lookup on....hope that makes sense...

HELP - Cheers

You really haven't given enough info to answer this I don't think. I suspect it's going to be some sort of CALCULATE(Max(table2[date field]), filter(table2,table2[ID]=EARLIER(table1[ID])&&table2,table2[date field]<=EARLIER(table1[date field])) to get the date value, then use that in LOOKUPVALUE.