Forum Discussion

KH_Mike's avatar
KH_Mike
Helper III
4 years ago
Solved

Identify the oldest record

Hi All,

 

I have a set of data like this. For the ID, they have duplication under different "Record Date". What I want to do is to create a new column to check if the "ID" have duplication, it will show "Y" for the oldest record and "N" for the remaining. For the "ID" without duplication, it will always show "Y". Thank you.

 

 

IDRecord DateExpected Result
A2022-06Y
A2022-07N
A2022-08N
B2022-07Y
C2022-06Y
D2022-06Y
B2022-08N

 

Need Help
  • PVO3's avatar
    PVO3
    4 years ago

    Hello,

     

    Try this

    Column =
    VAR _0 = 'Table'[ID]
    VAR _1 = CALCULATE(MIN('Table'[Record Date]), FILTER('Table',('Table'[ID] = _0)))
    RETURN
    IF(
        'Table'[Record Date] = _1,
        "Y",
        "N"
    )

1 Reply

  • PVO3's avatar
    PVO3
    Impactful Individual
    4 years ago

    Hello,

     

    Try this

    Column =
    VAR _0 = 'Table'[ID]
    VAR _1 = CALCULATE(MIN('Table'[Record Date]), FILTER('Table',('Table'[ID] = _0)))
    RETURN
    IF(
        'Table'[Record Date] = _1,
        "Y",
        "N"
    )