Tricky Match up of survey date in one table with survey answer in another

[ThaddeusB]

I have a table that lists client #s survey dates.  I also have separate tables last list answers to various questions.  I want to match them up to return the answer to a given question on the date of a survey.  The tricky part is there is not a one-to-one relationship between survey dates and answer dates.  For example, if a client was surveyed twice and the answer didn't change the answer table only has one row for that client.  So what I want to return is the newest answer on or before the survey date. Here is osme example data

Survey table

Survey #	Client	Survey date
1	1	2/1/2018
2	1	5/5/2018
3	2	3/4/2018
4	2	10/5/2018
5	3	4/4/2018
6	4	1/2/2018
7	4	5/7/2018
8	4	9/9/2018

Answer table

Client	Answer	Answer date
1	Yes	2/1/2018
2	No	1/1/2018
2	Yes	4/1/2018
2	No	9/1/2018
3	No	4/4/2018
3	Yes	5/30/2018
4	No	1/2/2018
4	Yes	9/9/2018

I would like something like a calculated column in the survey table with values like:

Survey #	Q1 Answer
1	Yes
2	Yes
3	No
4	No
5	No
6	No
7	No
8	Yes

Any suggestions?

[AlB · Accepted solution]

Hi @ThaddeusB

Try this for your new calculated column in the Survey table:

Q1Answer =
VAR _DateofInterest =
    CALCULATE (
        MAX ( Answer[Answer date] ),
        FILTER (
            Answer,
            Answer[client] = Survey[Client]
                && Answer[Answer date] <= Survey[Survey date]
        )
    )
RETURN
    LOOKUPVALUE (
        Answer[Answer],
        Answer[Client], Survey[Client],
        Answer[Answer date], _DateOfInterest
    )

Code formatted with