Forum Discussion
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?
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
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] )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!
