Calculate Mean Time Before Failure (MTBF) in different period of time

[josevarelac]

Hello, again. I´m here with a task that i want to do in Power BI. Now im having troubles with using DAX to soleve the following task.

Goal:

I need to obtain the Mean Time Before Failure (MTBF) of a machine or system on any period of time.

Backgrounds:

I´ve been looking for different topics or post that can give me some ideas of getting the task done. First, i seen a very good post of this topic. In that post you can find a very good picture os the MTFB indicator. The author use EARLIER() function to get the job done. Here is the link of the post:

https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339... 

Unlike the author, i need to use a "flexible" range of date. Being able to compare different dates mixing the dates on tables and calendar. Below is a full explanation of the idea.

Idea and Data:

My date is constructed in the following form where:

1.- Table named Test that has the information of failure of the machine, the descripction of the columns are the following:

Machine: Type of machine.

Stop Date: Date when the machine stop working.

Repair Date: Date shen the machine was repair.

Operation: The machine operating time.

Machine	Stop Date	Repair Date	Operation
A	10-09-2019 14:20	12-09-2019 15:30	24
A	15-10-2019 10:32	20-10-2019 4:30	24
A	11-11-2019 11:11	11-11-2019 12:30	24
B	12-07-2019 12:45	14-07-2019 12:56	24
B	18-09-2019 12:01	01-10-2019 0:00	24
B	02-10-2019 12:34	10-10-2019 4:50	24
B	12-11-2019 17:50	15-11-2019 0:00	24

2.- Table T, is a table that has the dates between first detention date and today:

T = CALENDAR(min(Test[Fecha Detención]);today())

VAR Fi = CALCULATE ( MIN ( T[Date] ); ALLSELECTED ( T[Date]) )

VAR Ft = CALCULATE ( MAX ( T[Date] ); ALLSELECTED ( T[Date]) )

I need to get the MTBF respect 2 given dates; Fi= Inicial date of evaluation and Ft=Final date of evaluation. So it is possible to be that <Fi < Stop Date> and <Ft > Repair Date> or any logical combination..  In the link that i showned before, the author don´t consider using Fi or Ft as "external" dates. That my main difference that i need to apply. So in order to extract MTBF i need to get the sum per machine, of all the times that the machine was on during Fi and Ft, considering the breakdowns.

MTBF is a function of Fi and Ft.

MTBF(Fi,Ft)= Sum of time that making was on during Fi and Ft/ Count of failures during Fi and Ft.

Taking that in mind,the scenarios/combination between Fi,Ff, Fd and Fr, i´ve describe the cases:

Fi = Initial date of evaluation

Ff = Final date of evaluation

Fd = Date of Detention

Fr =  Date of Repair

1) Fi<=Ft&&Fi<=Fd&&Fi<=Fr&&Ft<=Fr&&Ft<=Fd the Time of Functioning = 24*(Ft-Fi)

The fail pas before the evaluation period. So the making is working on the evaluation.

2)Fi<=Ft&&Fi<=Fd&&Fi<=Fr&&Ft<=Fr&&Ft>=Fd the Time of Functioning = 24*(Fd-Fi);

Fi is before the detention date (Fd) and Ft is before Repair time so the working time is the difference of: Fd-Fi.

3)Fi<=Ft&&Fi<=Fd&&Fi<=Fr&&Fd<=Ft&&Fr<=Ft the Time of Functioning = 24*(Fi-Fd+Ft-Fr);

The period of evaluation contains Fd and Ft or the fail pass in the time of evaluation, the Time working is = Fi-Fd+Ft-Fr.

4)Fi<=Ft&&Fd<=Fi&&Fr<=Fi&&Fd<=Ft&&Fr<=Ft the Time of Functioning = 24*(Ft-Fi);

The evaluation dates pasess before the fail, so the uptime is Ft-Fi.

5)Fi<=Ft&&Fd<=Fi&&Fi<=Fr&&Fd<=Ft&&Fr<=Ft the Time of Functioning =24*(Ft-Fr);0

The  Fd pases before Fi and the makin was fixed before Ff so time = Ft-Fr.

 There is an image of those cases:

 Case 1=a, 2=b, 3=c, 4=d and 5=e

The result is multiply be 24 cuz i want the time in hours. Also i need to be aware of the time that the machine is on between fails. 

Examples:

Here are 2 examples with different cases.

Nro 1:

Nro 2:

That my need, any help that you can share it is greatly apreciated.

Thank to all the comunity

Jose.

[josevarelac]

The image of the 1st case didn´t upload. Here is the image.