Find the last product purchased by customer ID

[kylon_19]

I have a bunch of transaction data. An individual may have multiple transactions over several years. What I need is to identify the last product they purchased based off a date field. The relevant fields don't necessarily all reside in the same table:

1. The product field is in one table

2. The id field is in another table

3. The date field is in a third table

Both the product field and the date field are connected to ID field table. 

ID   |   Product        |  Date

1    |  Product 1      |  1/1/18

1    |  Product 2      |  1/2/19

1    |  Product 3      |  2/3/19

2    |  Product 1      |  1/1/19

2    |  Product 1      |  4/8/19

What I want to see: 

ID   |   Product        |  Date

1    |  Product 3       |   2/3/19

2    |  Product 1       |   4/8/19

I had found this but it isn't working quite right for me. Not sure if it's even possible given everything resides in different tables and I can't create a new table since I don't have access to the power query editor for this dataset.

For each distinct string value, find the last date and tell me the value in the third column 

Thanks!

[edhans · Accepted solution]

I cannot help but think there is a cleaner way to do this, but here you go for now.

There is a valid date table with a one-to-many relationship single directional crossfiltering to the sales table.

Two measures as below:

Last Product Sale = 
CALCULATE(
    LASTDATE('Date'[Date]),
    FILTER(
        ALLEXCEPT('Sales Data','Sales Data'[ID]),
        'Sales Data'[Product] <> BLANK()
    ),
    CROSSFILTER('Date'[Date],'Sales Data'[Date],Both)
)

and

Last Product Name = 
CALCULATE(
    MAX('Sales Data'[Product]),
    FILTER(
        ALLEXCEPT('Sales Data','Sales Data'[ID]),
        'Sales Data'[Product] <> BLANK()
    ),
    CROSSFILTER('Date'[Date],'Sales Data'[Date],Both)
)

Returns this:

 See my PBIX file here to see the date table and the model.