date difference from list of related dates

[bice_cold]

I have 2 tables. The first looks like this:

EMPLOYEE TRANSFER TABLE

ID#          |         Employee      |       Prev. Employee  |      Transfer Date

12345                 John                          Sue                        Jan 1 2018

12346                 John                          Steve                     Jan 2 2018

12347                Greg                           Anne                     Feb 3 2018

12345                Steve                          John                      Mar 5 2018

and another table that looks like this:

SERVICE TERMINATION TABLE

ID#           |        Termination Date

12345       |        Mar 10 2018

12346       |        Mar 15 2018

The tables are related through the service ID notated as ID# here.

And what I need to return follows this logic:

If the termination date is less than 31 days after an employee transfer for the ID#, return the previous employee. Otherwise, return the current employee.

An example of my ideal output is this:

Employe Credited        |        Termination Date  |    Related ID #

John                             |            Mar 10 2018     |        12345

John                             |            Mar 15 2018     |        12346     

My trouble is that if an ID has multiple transfers I don't know how to best identify the date of the transfer that is closest to the termination date and check its distance from the termination date. Thanks in advance for any help you have to offer!

[v-chuncz-msft · Accepted solution]

@bice_cold,

You may add a calculated column to SERVICE TERMINATION TABLE.

Column =
VAR t =
    TOPN ( 1, RELATEDTABLE ( EMPLOYEE ), EMPLOYEE[Transfer Date], DESC )
RETURN
    MAXX (
        t,
        IF (
            DATEDIFF ( EMPLOYEE[Transfer Date], SERVICE[Termination Date], DAY ) < 31,
            EMPLOYEE[Prev. Employee],
            EMPLOYEE[Employee]
        )
    )

[bice_cold]

Thanks for your help, Sam. This was a great solution! I haven't used any variables like this before, so I'll need to spend some time learning about them but for now this worked.