Lookup a value based on date between start and end date

[htsvhwave]

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	Short period
John	4-1-2020	Long period
Chris	8-1-2019	Short period
Chris	15-1-2019	Medium period

Absence period

Employee	Period	Start Date	End Date
John	Short period	1-1-2019	3-1-2019
John	Long period	4-1-2020	29-1-2020
Chris	Short period	6-1-2019	9-1-2019
Christ	Medium period	12-1-2020	20-1-2019

[dk_dk]

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.

[danextian]

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]

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

[danextian]

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: