Forum Discussion

cplmak's avatar
cplmak
New Member
4 years ago
Solved

Lookup date between two columns and return a value

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 vio...
  • amitchandak's avatar
    amitchandak
    4 years ago

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

     

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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