Forum Discussion

summer18's avatar
summer18
Helper III
4 years ago
Solved

Get value based from max date

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

 

Need Help
  • goncalogeraldes's avatar
    goncalogeraldes
    4 years ago

    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

     

2 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    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