Vlookup most recent record from another table

[Anonymous]

Hello!

I need to add a column to my table here for "Boat Offered" which is in my Mailers table.

Based on how the data model is setup, I know I need some dax, but not sure how to ensure what's returned is in fact:

1 - Before the date of Purchase in the Sales Table

2 - After looking at date of purchase, returning the "Boat Offered" in the most recent mailer sent

I have an in between table called Customers which I'm using to connect to both Sales and Mailers since I can't connect directly between Mailers and Sales

Link to PBI File

https://1drv.ms/u/s!AuheBS0uRNVlmDuvvA9tFfKPd_X7

Sales TableMailer TableTable Needing Appending (Customer Table)

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

1.Create column in "Sales" table

max date = CALCULATE(MAX(Sales[DealDate]),ALLEXCEPT(Sales,Sales[CustomerID]))

2.Create a new table

Table = SUMMARIZE(Sales,Sales[CustomerID],Sales[max date])

3.Create relationship as below

4.create a column in "Mailers" table

related_max = RELATED('Table'[max date])

5.create measures in "Mailers" table

Measure 2 = CALCULATE(MAX(Mailers[Campaign_Sent_Date]),ALLEXCEPT(Mailers,Mailers[CustomerID]))

Measure 3 =
IF (
    MAX ( Mailers[related_max] ) <> BLANK (),
    CALCULATE (
        MAX ( Mailers[Campaign_Sent_Date] ),
        FILTER (
            ALLEXCEPT ( Mailers, Mailers[CustomerID] ),
            Mailers[Campaign_Sent_Date] < MAX ( Mailers[related_max] )
        )
    )
)

Measure 4 =
IF (
    MAX ( Mailers[CustomerID] ) <> BLANK ()
        && MAX ( Mailers[related_max] ) <> BLANK ()
        && [Measure 3] <> BLANK (),
    CALCULATE (
        MAX ( Mailers[Boat Offered] ),
        FILTER (
            ALLEXCEPT ( Mailers, Mailers[CustomerID] ),
            Mailers[Campaign_Sent_Date] = [Measure 3]
        )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much!