Forum Discussion

ReiKam's avatar
ReiKam
New Member
9 years ago
Solved

Search for a value using date ranges

I need to look up a value in one table based on date ranges. I have the following setup.   Table Watches Date Employee Start Stop Department   Table Watches is a table showin...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi ReiKam,

    Just create a calculated column using the following formula in your Sales table.

    Dep = CALCULATE(FIRSTNONBLANK(Watches[Department],Watches[Department]),FILTER(Watches,Watches[Start]<=Sales[Time]&&Watches[Stop]>=Sales[Time]&&Watches[Employee]=Sales[Employee]&&Watches[Date]=Sales[Date]))


     

    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi ReiKam,

Just create a calculated column using the following formula in your Sales table.

Dep = CALCULATE(FIRSTNONBLANK(Watches[Department],Watches[Department]),FILTER(Watches,Watches[Start]<=Sales[Time]&&Watches[Stop]>=Sales[Time]&&Watches[Employee]=Sales[Employee]&&Watches[Date]=Sales[Date]))


 

Thanks,
Lydia Zhang

ReiKam's avatar
ReiKam
New Member
9 years ago

Hi Lydia,

 

Thanks a lot, it works like a dream. You've really saved my day and my week too. :)

 

Regards

Reinert