repair metrics MTTR MTBF

[inderz]

Can anyone guide me how to calculate MTBF, MTTR - (Repair Metrics) - from a running datset as under :-

[OwenAuger · Accepted solution]

Hi @inderz

Here are some ideas on how to calculate these metrics, based on my interpretation. Take these as suggestions that may need to be adapted 🙂

Here is my sample pbix file.

First of all, some Googling would suggest those two abbreviations stand for:

1. MTTR = Mean time to repair
2. MTBF = Mean time between failures

Hopefully that's correct.

In the below I'm going to call your table Repairs, and also assume that all columns in your example are present, except Days for Repair which can be calculated.

1. For MTTR, I would calculate the simple average of (Date Out - Date In) over the rows of Repairs:
   

   Mean Time To Repair = 
   AVERAGEX (
   	Repairs,
   	1 * ( Repairs[Date Out] - Repairs[Date In] )
   )

   The multiplication by 1 is needed to force a number to be returned, rather than a date.
   
   
2. For MTBF, I would:
   1. Iterate through the rows of Repairs
   2. For each row, find the latest Date Out that is on or before the current row's Date In, for the same Machine ID but for a different S No (
   3. If such a Date Out exists, calculate the difference between this Date Out and the current row's Date In.
   4. Calculate the mean of all differences calculated in step 3 (for only those rows where a difference could be calculated).
      

      Mean Time Between Failures = 
      AVERAGEX (
          Repairs,
          VAR CurrentDateIn = Repairs[Date In]
          VAR CurrentSNo = Repairs[S No]
          VAR PreviousDateOut =
              CALCULATE (
                  MAX ( Repairs[Date Out] ),
                  ALLEXCEPT ( Repairs, Repairs[Machine ID] ),
                  Repairs[Date Out] <= CurrentDateIn,
                  Repairs[S No] <> CurrentSNo
                  // If [S No] is increasing with time, then the <> could be changed to <
                  // This might be safer, in case you ever have two repairs starting/ending
                  // on the same day for one machine.
              )
          RETURN
              IF (
                  NOT ( ISBLANK ( PreviousDateOut ) ),
                  1
                      * ( CurrentDateIn - PreviousDateOut )
              )
      )

      MTBF will be blank where no previous failure existed for a particular machine, and only rows where it is nonblank will be included in the average.

The output looks like this:

Anyway, hopefully this is an indication of how you could calculate this, even if I have mis-interpreted some conventions of these metrics.

Regards,

Owen

[Anonymous]

Hi

I am trying to calculate the MTTR using this formula

CALCULATE(AVERAGEX('Report 1','Report 1'[Actual Labor Hours]),FILTER('Report 1','Report 1'[Actual Labor Hours]>0))

it is not giving me back the correct results. It also look like that if I do a count of the rows containg the values (like in a excel pivot table) the results are not right. 

my table look like. and I simply want to calculate the average for A,B,C etc

Location   Labour

A	5
B	6
C	0
C	4
D	5
A	6
B	4
R	2
R	5
R	4
R	7

[Selded]

@inderz 

Please what does S No mean in this table as i am trying to use the solution provided you

[OwenAuger · Accepted solution]

Hi @inderz

Here are some ideas on how to calculate these metrics, based on my interpretation. Take these as suggestions that may need to be adapted 🙂

Here is my sample pbix file.

First of all, some Googling would suggest those two abbreviations stand for:

1. MTTR = Mean time to repair
2. MTBF = Mean time between failures

Hopefully that's correct.

In the below I'm going to call your table Repairs, and also assume that all columns in your example are present, except Days for Repair which can be calculated.

1. For MTTR, I would calculate the simple average of (Date Out - Date In) over the rows of Repairs:
   

   Mean Time To Repair = 
   AVERAGEX (
   	Repairs,
   	1 * ( Repairs[Date Out] - Repairs[Date In] )
   )

   The multiplication by 1 is needed to force a number to be returned, rather than a date.
   
   
2. For MTBF, I would:
   1. Iterate through the rows of Repairs
   2. For each row, find the latest Date Out that is on or before the current row's Date In, for the same Machine ID but for a different S No (
   3. If such a Date Out exists, calculate the difference between this Date Out and the current row's Date In.
   4. Calculate the mean of all differences calculated in step 3 (for only those rows where a difference could be calculated).
      

      Mean Time Between Failures = 
      AVERAGEX (
          Repairs,
          VAR CurrentDateIn = Repairs[Date In]
          VAR CurrentSNo = Repairs[S No]
          VAR PreviousDateOut =
              CALCULATE (
                  MAX ( Repairs[Date Out] ),
                  ALLEXCEPT ( Repairs, Repairs[Machine ID] ),
                  Repairs[Date Out] <= CurrentDateIn,
                  Repairs[S No] <> CurrentSNo
                  // If [S No] is increasing with time, then the <> could be changed to <
                  // This might be safer, in case you ever have two repairs starting/ending
                  // on the same day for one machine.
              )
          RETURN
              IF (
                  NOT ( ISBLANK ( PreviousDateOut ) ),
                  1
                      * ( CurrentDateIn - PreviousDateOut )
              )
      )

      MTBF will be blank where no previous failure existed for a particular machine, and only rows where it is nonblank will be included in the average.

The output looks like this:

Anyway, hopefully this is an indication of how you could calculate this, even if I have mis-interpreted some conventions of these metrics.

Regards,

Owen

[Anonymous]

Could you explain what "S no" stands for? I dont get also how you can have, at the same machine, another fail meawhile you havent fixed a previous fail.

Thanks,

Erick

[inderz · Accepted solution]

Owen....that is just what I needed !

Thank you ever so much for being a life-saver !

Will go through and apply the same to my dataset and revert in case I need some help.

Cheers 

Inder 

[rennda]

Hey @OwenAuger , 

i´m hopefull you can help me with my actual issue. I tried to get your code in my table to calculate the MTBF. But it results the same Value in all rows. 

I can´t find the source of this incorrect output.

Anyone can help ? 🙂