Lookup function for max date

[ThomasWeppler]

Hi power Bi community

I have two tables and I need to get data from one of them to the other.

The tables are called Data and chechkin.

In the data table I have a the following rows.

Index: (number)

Customer_id: (number)

Answer_date: (date)

The chechkin table have the following rows

Index: (number)

Customer_id: (number)
Chechkin_date: (date)

I want to make a lookup function where I find the last chechin_date in the chechkin_table with a date that comes before the answer date.

I have tried with this Dax:

LastChechIndate = lookupvalue('Checkin'[Chechkin_date], 'Checkin'[Customer_id], 'Data'[Customer_id])

The problem is that I get an error becuase the Dax returns multiple values. Can anyone help me with this issue?
All help is greatly appreciated.

[aduguid · Accepted solution]

Create a calculated column in the Data table to store the last Chechkin_date.

LastChechkinDate = 
VAR CurrentCustomerID = 'Data'[Customer_id]
VAR CurrentAnswerDate = 'Data'[Answer_date]
RETURN
    MAXX(
        FILTER(
            'Checkin',
            'Checkin'[Customer_id] = CurrentCustomerID &&
            'Checkin'[Chechkin_date] < CurrentAnswerDate
        ),
        'Checkin'[Chechkin_date]
    )

[ThomasWeppler]

Thanks for the answer.