Adding specific Column from another table using date filter between two dates

[Kaitra]

Hello everybody,

I have a little situation where I need your help.

The current situation is, that I have two different tables:

Table1

Name	Booking Date	Hours
Person 1	01.01.2022	160
Person 1	01.02.2022	150
Person 2	01.01.2022	145
Person 1	01.03.2022	160
Person 2	01.03.2022	170
Person 2	01.02.2022	165

Table2

Name	Start date	End date	ID
Person 1	01.01.2022	01.02.2022	00001
Person 1	01.03.2022	01.04.2022	00002
Person 2	01.01.2022	01.06.2022	00003

I'd like to have the ID from Table 2 in Table 1 depending on the Booking Date in Table 1. For example:

Name	Booking Date	Hours	ID
Person 1	01.01.2022	160	00001
Person 1	01.02.2022	150	00001
Person 1	01.03.2022	160	00002

I have tried to create a calculated column like this:

Contract ID =

CALCULATE (

VALUES ( 'Table2'[ID] ),

FILTER (

'Table2',

'Table2'[Start Date] <= EARLIER ( 'Table1'[Booking Date] )

&& 'Table2'[End Date] >= EARLIER ( 'Table1'[Booking Date )

&& 'Table2'[Name])

)

But this wasn't working, because the name cannot converted of type Text to type True/False.

I hope someone can help me out.

Cheers and thanks a lot.

[johnt75]

You can add a calculated column like

ID = 
var currentPerson = 'Table1'[Name]
var currentDate = 'Table1'[Booking date]
return CALCULATE( LOOKUPVALUE( 'Table2'[ID], 'Table2'[Name], currentPerson),
    'Table2'[Start date] <= currentDate && 'Table2[End date] >= currentDate
)

[Kaitra]

Thanks for the quick response, unfortunately it was not working for me. I get the error "A table of multiple values was supplied where a single value was expected"

[johnt75 · Accepted solution]

Sounds like a person has multiple entries in Table2 with overlapping dates, so the booking cannot determine which ID is correct.

You could try something like

ID = 
var currentPerson = 'Table1'[Name]
var currentDate = 'Table1'[Booking date]
return CALCULATE( MIN( 'Table2'[ID]), 'Table2'[Name] = currentPerson),
    'Table2'[Start date] <= currentDate && 'Table2[End date] >= currentDate
)

which will try and return the first booking which matches.

[Kaitra]

That was working, thanks a lot!!