Lookup a value based on date between start and end date

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

The solution I can think of is not very elegant but it should work:

Follow the tutorial here 

https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

to transform your Absence Period table in query (duplicate/reference the original query in case you want to keep the original format intact for other visuals or measures).

In the transformed Absence period create a Key column which concatenates Employee and the Date column that results from the transformation.

Create the same column in Timesheets table. Then you should be able to use lookupvalue, or connect the two tables together in the model and get the period directly from your transformed Absence period table.

Let me know if you have any questions.