Forum Discussion

RoseyAlexa's avatar
RoseyAlexa
Helper I
6 years ago

Lookup value based on date between a range

I have two tables: Timesheets - has staff hours and the date they worked those hours Career History - has a Start Date and End Date that an employee worked at a particular Branch   In the Timeshe...
JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

Hi RoseyAlexa ,

First off: no judgement from my side! 🙂 Everybody had to start at some point in PowerBI and most of us here are having fun helping people out with their specific cases, I learn a lot by helping others myself for example 🙂

Back to your case; thanks for sharing the model! That really clarifies a few things for me. Could you please share the DAX of the calculated column you used? There might be a litle mistake when translating my solution to your specific column- and table names.

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

RoseyAlexa's avatar
RoseyAlexa
Helper I
6 years ago

JarroVGITthanks for helping.

Here is what I translated your code into:

 

Timesheet Branch =
VAR staffID = 'Timesheets'[Staff_ID]
VAR timeSheetDate = 'Timesheets'[Timesheet Date]
RETURN
CALCULATE(SELECTEDVALUE('Career History'[Branch], "Multi"), FILTER('Career History', 'Career History'[Staff_ID] = staffID && 'Career History'[Career History Start Date] <= timeSheetDate && OR('Career History'[Career History End Date] = BLANK(), 'Career History'[Career History End Date]> timeSheetDate)))
  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    That seems to be correct. Is it possible to share your PBIX? You can PM me the link and I will have a look, I think the datamodel is different then that I currently think.