Fuzzy Date Match

[DreDre]

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?

[AlexisOlson · Accepted solution]

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]
)

[DreDre]

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!