Nearest previous date

[afhs]

Hi,

I have two tables witg data about locations and tasks. I'm trying to import from Table2, the nearest previous date based on the planned date on Table1 for the Location.
Any ideas on how can I find the nearest previous date from another column based on the "Location"? 

Table1

Location	Task	Planned Date
Home 1	Cleaning	03-12-2019
Home 1	Painting	19-12-2019
Home 2	Painting	05-12-2019
Home 2	Mowing	29-12-2019
Home 3	Cleaning	17-12-2019
Home 5	Cleaning	18-12-2019
Home 7	Mowing	22-12-2019
Home 8	Mowing	29-12-2019

Table2

Location	Task Completition
Home 1	01-12-2019
Home 1	15-12-2019
Home 2	01-12-2019
Home 2	23-12-2019
Home 3	12-12-2019
Home 5	14-12-2019
Home 7	19-12-2019
Home 8	23-12-2019

The resulting table should looks as follow:

Location	Task	Planned Date	Task Completition
Home 1	Cleaning	03-12-2019	01-12-2019
Home 1	Painting	19-12-2019	15-12-2019
Home 2	Painting	05-12-2019	01-12-2019
Home 2	Mowing	29-12-2019	23-12-2019
Home 3	Cleaning	17-12-2019	12-12-2019
Home 5	Cleaning	18-12-2019	14-12-2019
Home 7	Mowing	22-12-2019	19-12-2019
Home 8	Mowing	29-12-2019	23-12-2019

Thanks

[Anonymous · Accepted solution]

@afhs Please use below measure to get the latest completion date

Measure = 
VAR _location = MAX(Table1[Location])
VAR _plannedDate = MAX(Table1[Planned Date])
RETURN CALCULATE(MAX(Table2[Task Completition]),Table2[Location]=_location,Table2[Task Completition]<=_plannedDate)

If it helps accept solution.