Related table last occurance

[Anonymous]

I am fairly new to power bi, but experienced in MSAccess vba. This project has been a bit of a challenge for me.

I have 2 tables, Contracts & Tickets. I am trying to find the contract number with a start date that was just prior to the ticket opened date.

Contracts:

Itm	Contract	StartDt	EndDt
Aaa	123	12/1/18	12/3/18
BBB	124	12/1/18	12/4/18
BBB	126	12/5/18	12/12/18
Aaa	128	12/6/18	12/10/18

Ticket:

TickNbr	Itm	Opened
5678	Aaa	12/4/18
5679	Aaa	12/7/18
5680	BBB	11/30/18

Results:

TickNbr	Itm	Contract
5678	Aaa	123
5679	Aaa	128
5680	BBB	Null

Any help would be appreciated!

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this as a calculated column in Ticket Table

Calc Column in Ticket Table =
MINX (
    TOPN (
        1,
        FILTER (
            Contracts,
            Contracts[Itm] = Ticket[Itm]
                && Contracts[StartDt] < Ticket[Opened]
        ),
        Contracts[StartDt], DESC
    ),
    [Contract]
)

[Anonymous]

Thank you! Worked perfect!