Measure to show if a PO Number ID was used in the past

[Anonymous]

Hi Experts

Need a measure to show if a PO Number ID was used in the past and if it was the show the result as shown in the table below...

PO Number	Date	When Used In the Past
0000039	16/12/2021
0000039	18/02/2022	16/12/2021
0000354	20/03/2020
0000354	17/09/2021	20/03/2020

[jdbuchanan71 · Accepted solution]

@Anonymous 

Give this a try.

When Used In the Past = 
VAR _CurrentDate = SELECTEDVALUE ('Table'[Date] )
RETURN
CALCULATE(
    MAX ('Table'[Date] ),
    ALLEXCEPT('Table','Table'[PO Number]),
    'Table'[Date] < _CurrentDate
)

[ribisht17]

@Anonymous 

Prev to Max = CALCULATE(MAX(Sheet3[Date]),FILTER(all(Sheet3),Sheet3[Date] < max(Sheet3[Date])),FILTER(all(Sheet3),Sheet3[PO Number]=max(Sheet3[PO Number])))

I added one row from my side to check if it works for more than 2 IDs as well

Regards,

Ritesh