Forum Discussion

htsvhwave's avatar
htsvhwave
Icon for Helper II rankHelper II
2 years ago

Lookup a value based on date between start and end date

Hi i want help to calute the period column in the timesheet, which can be found in the absence period tabel, how do i do that Timesheets Employee Date  Period John 3-1-2019 Sh...
Need Help
Show and Tell
danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi htsvhwave ,

Please try this:

Period2 = 
CALCULATE (
    MAX  ( 'Absence period'[Period] ),
    FILTER (
        'Absence period',
        'Absence period'[Employee] = EARLIER ( Timesheets[Employee] )
            && EARLIER ( Timesheets[Date] ) >= 'Absence period'[Start Date]
            && EARLIER ( Timesheets[Date] ) <= 'Absence period'[End Date]
    )
)

 

Please take note of the following in your sample data:

No result for row 4 as your start date > than your end date

Christ instead of Chris

 

 

  • htsvhwave's avatar
    htsvhwave
    Icon for Helper II rankHelper II
    2 years ago

    When i try to use your fomula i cant call the column after using the Earlier function

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      2 years ago

      Which table are you entering this formula in? And also, this is supposed to be a calculated column and not a measure. You can't use EARLIER within a measure unless it is used within  a virtual table.

      Please see attached sample pbix for details: