For a certain value, check its date and return its prior date

[Anonymous]

I have a list of serial numbers and the date in which they were serviced.

I also did a formula that is identifying the first repair and the duplicate repairs.

But what I would need now is to know the date in which the prior repair came in

Serial          Date Serviced             Repeat?

A                 01/01/2019                First Repair

B                 01/01/2019                First Repair

A                 01/05/2019                Duplicate Repair

A                 01/07/2019                Duplicate Repair

The expected outcome would be like this

Serial          Date Serviced             Repeat?                        Prior Repair Date

A                 01/01/2019                First Repair                   -

B                 01/01/2019                First Repair                   -

A                 01/05/2019                Duplicate Repair           01/01/2019

A                 01/07/2019                Duplicate Repair           01/05/2019

Would appreicate your insights on this! I have already searched plenty but could not find something that fits the situation.

Thank you!

[AlB · Accepted solution]

Hi @Anonymous 

Try this:

Prior Repair Date =
CALCULATE (
    MAX ( Table1[Date Serviced] ),
    Table1[Date Serviced] < EARLIER ( Table1[Date Serviced] ),
    ALLEXCEPT ( Table1, Table1[Serial] )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Anonymous]

@AlB You are the greatest! Thank you, thank you, thank you!