Calculate Age of spare part between failures

[KvO88]

I want to know the lifetime of a spare part based on dates in different rows. Example: the age of the spare part after first replacement will be "date of replacement - installation date". When that same part is replaced again on the same system, I want to calculate the age since the replacement "date of 2nd replacement-date of 1st replacement" and so on. 

See below a simplified example of a table containing the replacements of a single spare part:

Unique System ID	Spare Part ID	System Install Date	Part Replacement Date	Age of part?
1	5330 018	01-Jan-2023	01-Feb-2023
2	5330 018	23-Feb-2023	05-Apr-2023
3	5330 018	04-Mar-2023	08-Aug-2023
4	5330 018	12-May-2023	3-Sep-2023
1	5330 018	01-Jan-2023	19-Nov-2023
2	5330 018	23-Feb-2023	18-Nov-2023
5	5330 018	17-Jul-2023	23-Nov-2023
1	5330 018	01-Jan-2023	01-Jan-2024
6	5330 018	05-Sep-2023	18-Jan-2024
3	5330 018	04-Mar-2023	5-Oct-2023

[v-yifanw-msft · Accepted solution]

Hi @KvO88 ,
I believe that @Greg_Deckler 's answer will be of great help to you and in the meantime I have created some data for you to help you and I hope that the combination of our answers will help you to solve your problem. You can follow the steps below:

1.Add a new column.

Age of Part = 
VAR _ID = 'Table'[Spare Part ID]
VAR _Date = 'Table'[Part Replacement Date]
RETURN
DATEDIFF(
    CALCULATE(
        MAX('Table'[Part Replacement Date]),
        FILTER(
            'Table',
            'Table'[Spare Part ID] = _ID
            && 'Table'[Part Replacement Date] < _Date
        )
    ),
    _Date,
    DAY
)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KvO88]

Hi @v-yifanw-msft ,

This still does not calculate the age of the part until the first time it was replaced, so the datediff install date, first time replaced. Do you know how I can integrate that in this approach?

[Greg_Deckler]

@KvO88 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
 ( __Current - __Previous ) * 1.

[KvO88]

Hi @Greg_Deckler,

Thank you, I did find your article very helpful. There is one thing that I am struggeling with, what I cannot get right is that the first meantime between failure should be the date a first time a repair was needed minus (-) the installation date. That is currently not in the "Uptime" calculation if I am correct.  

Can you help me a little further with that?