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...
RoseyAlexa's avatar
RoseyAlexa
Helper I
6 years ago

The real dataset is much larger. There are multiple entries for any one person on any one day, as they will have put time to a number of projects.

The tables I provided were just example tables created in the forum post.

I've checked and, yes, they are date fields.

 

I've tried adding other data, to replicate it a little closer to the large dataset, and creating the same relationships as my main dataset.

But your PBIX is still working, and my main dataset is not.

 

Must be something in the relationships I think.
Dont judge me on my table structures. I'm new at this stuff

click here for table relationship image

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.