Calculating Mean Time Between Failure

Hello

@Amit, @Greg , @tamerj1 , @lbendlin 

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

@christinepayton @audreygerred 

@LukeB,

I have a data table called pmAsset where it has a List of Equipment that has never failed.

I have another data table Table2 where it has a List of Equipment that has failed with a specific Breakdown Date.

I have adopted the following methodology to calculate Mean Time Between Failures:

I have merged the pmAsset and Table2 dataset based on common variable- ‘Equipment’ in Table2 and variable ‘Code’ in pmAsset based on Inner Join(only matching rows).

pmAsset data table has a variable called StartDate that shows the dates when Assets were commissioned.

After that, I wrote the following Calculated Column to calculate Mean Time Between Failure:

TimeBetweenFailures = DATEDIFF(MergedTable[pmAsset.StartDate],MergedTable[BreakDownDate],DAY)

MTBF = AVERAGE(MergedTable[TimeBetweenFailures])

After that, I put BreakdownDate on the X-axis and MTBF on the Y-axis on a StackedColumnChart but this gave me very unconvincing results for MTBF where I got a constant value of 4700 days for all the months. So, I know this is incorrect.

I then adopted another methodology to calculate Mean Time Between Failure:

Operating Time = DATEDIFF('MergedTable'[StartDate].[Date],'MergedTable'[BreakDownDate].[Date],DAY)

TotalOperatingTime = SUM(MergedTable[Operating Time])

Number of Failures = COUNT(MergedTable[BreakDownDate])

Mean Time Between Failures = DIVIDE('MergedTable'[TotalOperatingTime],'MergedTable'[Number of Failures])

After that, I put BreakdownDate on the X-axis and MTBF on the Y-axis on a StackedColumnChart but this gave me very unconvincing results for MTBF where I got weird set of values like either null or -31, -29 etc..

Can you please suggest the right methodology to calculate the Mean Time Between Failures?

pmAsset data table and Table2 data table has many other variables like BreakDownDate, DueDate, CompletionDate, StartDate, RepairStartDate, RepairCompletedDate etc..

Also, can you please suggest-what values to use in the X-axis—BreakDownDate or DueDate or CompletionDate or StartDate?

Also, do I need to take other variables into consideration like RepairStartDate or RepairCompletedDate to calculate the Mean Time Between Failures?