cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DreDre
Helper II
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

CityBank#Start DateEnd DateStart TimeEnd Time
Dallas101/01/20207/31/202000:0008:00
Dallas2

01/01/2020

7/31/202008:0116:30
Dallas301/01/20207/31/202016:3123:59
Dallas16/1/202012/31/202000:0010:00
Dallas26/1/202012/31/202010:0117:30
Dallas36/1/202012/31/202017:3100:00
Austin11/1/2020

7/31/2020

00:0011:30
Austin21/1/20207/31/202011:3120:30

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

 

 

Data_Table

CityDateTime(New Bank Column - Future State)
Dallas2/5/202011:522
Dallas7/20/202008:001
Austin4/2/202011:251
Dallas10/10/202022:003

 

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
AlexisOlson
Super User
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]
)

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
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]
)

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!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors