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 ,

This should meet your requirements. 

Branch = 
VAR staffID = Timesheet[Staff_ID]
VAR timeSheetDate = Timesheet[TimesheetDate]
RETURN
CALCULATE(SELECTEDVALUE(Careerhistory[Branch], "Multi"), FILTER(Careerhistory, Careerhistory[Staff_ID] = staffID && Careerhistory[StartDate] <= timeSheetDate && OR(Careerhistory[EndDate] = BLANK(), Careerhistory[EndDate] > timeSheetDate)))

Result:

 

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

Nearly right, but it returns a value of Multi for nearly every row.

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    Please find my PBIX here where this solution fits your requirements applied to the testdata your provided. If the solution has a different outcome in your real dataset, then the difference between the your dataset and the dataset you provided to us are likely the cause of the malfunctioning. Without knowing what your real dataset looks like, there is very little I can do? 

    The only thing I can think of: are your date columns of the type Date? (They weren't when I copied your test data into PBI)

     

    https://1drv.ms/u/s!Ancq8HFZYL_aiIoUNv6XLc0V5ONqyg?e=kRTFV9 

     

    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

      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! 🙂