cancel
Showing results for
Did you mean:
Helper II

## Fuzzy Date Match

I know there is a solution for this, but I keep getting lost in the variations, but In essence I am trying to do an vlookup with 2 lookup values in one table referencing a lookup table.

My goal is to add the associated bank number based on the date/city/time combo in Data_Table and add that number as a new column. I have seen multiple variations, but I am trying to do this with a single calculated column. I think that means I need to use VAR for part of the formula, but I keep getting lost.

Bank_Table

 City Bank# Start Date End Date Start Time End Time Dallas 1 01/01/2020 7/31/2020 00:00 08:00 Dallas 2 01/01/2020 7/31/2020 08:01 16:30 Dallas 3 01/01/2020 7/31/2020 16:31 23:59 Dallas 1 6/1/2020 12/31/2020 00:00 10:00 Dallas 2 6/1/2020 12/31/2020 10:01 17:30 Dallas 3 6/1/2020 12/31/2020 17:31 00:00 Austin 1 1/1/2020 7/31/2020 00:00 11:30 Austin 2 1/1/2020 7/31/2020 11:31 20:30

etc. this table continues on and covers a few years and multiple cities worth of data.

Data_Table

 City Date Time (New Bank Column - Future State) Dallas 2/5/2020 11:52 2 Dallas 7/20/2020 08:00 1 Austin 4/2/2020 11:25 1 Dallas 10/10/2020 22:00 3

Request:

Can someone please help me with the DAX formula to populate the Bank Column based on the Bank_Table as a lookup?

1 ACCEPTED SOLUTION
Super User

Like this?

``````New Bank Calculated Column =
CALCULATE (
SELECTEDVALUE ( Bank_Table[Bank#] ),
Bank_Table[Start Date] <= Data_Table[Date],
Bank_Table[End Date]   >= Data_Table[Date],
Bank_Table[Start Time] <= Data_Table[Time],
Bank_Table[End Time]   >= Data_Table[Time]
)``````
2 REPLIES 2
Super User

Like this?

``````New Bank Calculated Column =
CALCULATE (
SELECTEDVALUE ( Bank_Table[Bank#] ),
Bank_Table[Start Date] <= Data_Table[Date],
Bank_Table[End Date]   >= Data_Table[Date],
Bank_Table[Start Time] <= Data_Table[Time],
Bank_Table[End Time]   >= Data_Table[Time]
)``````
Helper II

I knew this had to be easier than I was making it out to be! Thank-you so much for your help @AlexisOlson this worked perfectly!