Get value based from max date

[summer18]

Hi,

I need help on how can I get the value from "Type" column based from max Purchased Date per Product Number and ProductLineNum.  For example, for Product Number 5100, the max Purchased Date is 10/25/2019 so it should return the value of "Major equipment".

Expected output:

I'm trying VAR and RETURN formula but I could not get it right.

Thank you in advance

[goncalogeraldes · Accepted solution]

Hello there @summer18 ! Try this:

Latest Type =
CALCULATE (
    MAX ( Table[Type] ),
    FILTER (
        'Table',
        'Table'[Purchased Date]
            = CALCULATE (
                MAX ( 'Table'[Purchased Date] ),
                FILTER ( Table, 'Table'[Product Number] = EARLIER ( 'Table'[Product Number] ) )
            )
            && [Product Number] = EARLIER ( [Product Number] )
    )
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[summer18]

Thank you very much @goncalogeraldes !  It works!