lookup nearest date

[Anonymous]

Table 1
Name	Date in Service	First Project	Second Project
Alex K	1-1-2018	I want here the first project  of Table 2 of Alex K based on project start date vs Date In service	Here  the second
Jay kay	1-2-2018
Bernd leo	1-3-2018
Table 2
Name	name Project	Project Start Date	Project End Date
Alex K	AA technology	2-1-2018	14-2-2018
Alex K	AB technology	15-2-2018	11-3-2018
Alex K	AC technology	12-3-2018	14-2-2018
Jay kay	AA technology	15-2-2018	19-2-2018
Jay kay	AB technology	20-2-2018	11-3-2018
Jay kay	AC technology	12-3-2018	14-4-2018
Jay kay	AD technology	15-4-2018	1-3-2018
Bernd leo	AA technology	2-3-2018	11-3-2018
Bernd leo	AB technology	12-3-2018	14-3-2018
Bernd leo	AC technology	15-3-2018

i have 2 tables and in table one i want to  have the first and scond project of table 2 based on the nearest Project start date in comparison with the date in service

example: Alex K first project is AA technology because 2-1-2018 is the first nearest date tot the date in service 1-1-2018

kind regards

[Anonymous · Accepted solution]

Thank so much! 

I also need the start date of the first and second project.

Can you help me with it?

[Zubair_Muhammad]

@Anonymous

Try these calculated columns

First Project =
VAR mydate =
    CALCULATE (
        MIN ( Table2[Project Start Date] ),
        FILTER (
            CALCULATETABLE ( VALUES ( Table2 ) ),
            Table2[Project Start Date] >= Table1[Date in Service]
        )
    )
RETURN
    CALCULATE (
        MIN ( Table2[name Project] ),
        FILTER (
            CALCULATETABLE ( VALUES ( Table2 ) ),
            Table2[Project Start Date] = mydate
        )
    )

[Zubair_Muhammad]

@Anonymous

Second Project =
VAR FirstProject = Table1[First Project]
VAR mydate =
    CALCULATE (
        MIN ( Table2[Project Start Date] ),
        FILTER (
            CALCULATETABLE ( VALUES ( Table2 ) ),
            Table2[Project Start Date] >= Table1[Date in Service]
        ),
        Table2[name Project] <> FirstProject
    )
RETURN
    CALCULATE ( MAX ( Table2[name Project] ), Table2[Project Start Date] = mydate )

[Anonymous · Accepted solution]

Thank so much! 

I also need the start date of the first and second project.

Can you help me with it?

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try with these.

Date First Project =
CALCULATE (
    MIN ( Table2[Project Start Date] ),
    FILTER (
        CALCULATETABLE ( VALUES ( Table2 ) ),
        Table2[Project Start Date] >= Table1[Date in Service]
    )
)

Date Second Project =
CALCULATE (
    MIN ( Table2[Project Start Date] ),
    FILTER (
        CALCULATETABLE ( VALUES ( Table2 ) ),
        Table2[Project Start Date] >= Table1[Date in Service]
    ),
    Table2[name Project] <> FirstProject
)