Lookup date between two columns and return a value

[cplmak]

Hi. I have a conundrum. I have two tables, Violations and Shift Calendar. Id like to be able to look up the RPTTIME in the Violations table against the Shift Calendar to determine which shift the violation occured. Is this possible?

Violations table

Shift Calendar

Result

Thanks

[amitchandak · Accepted solution]

@cplmak , New column in DAX

Shift = maxx(filter(Table2, Table1[RPTTIme] >= Table2[Start Time] && Table1[RPTTIme] < Table2[End Time]), Table2[Shift])

Weeknum = maxx(filter(Table2, Table1[RPTTIme] >= Table2[Start Time] && Table1[RPTTIme] < Table2[End Time]), Table2[Weeknum])