Forum Discussion

josevarelac's avatar
josevarelac
Frequent Visitor
6 years ago
Solved

Get total hours functioning in a period

Hello comunity.   I need help to get the total operation/working time of a machine in a given period of time in Power Bi.  The date is form in the following form: Machine Detention Date Repa...
Need Help
Tips and Tricks
josevarelac's avatar
josevarelac
Frequent Visitor
6 years ago

v-juanli-msft finally i got with the solution. 

 

Operation Time = Total Working Time- Repair Time. Then, count the fails (N) that falls in the period selected and finally.

 

MTBF = Operation Time/ N

 

The code for Total Working Time:

 

Op. Time = 
VAR Fi =  CALCULATE ( MIN ( T[Date] ); ALLSELECTED ( T[Date]) )
VAR Ft = CALCULATE ( MAX ( T[Date] ); ALLSELECTED ( T[Date]) )
VAR Fd =  sum(Test[Funcionamiento ])*(Ft-Fi)/COUNT(Test[Funcionamiento ]) 
return Fd

  For each column it calculate the sum, so i divided to get the Operation time per machine.

 

Then for Repair Time:

Time Down = 
VAR Fi = CALCULATE ( MIN ( T[Date] ); ALLSELECTED ( T[Date]) )
VAR Ft = CALCULATE ( MAX ( T[Date] ); ALLSELECTED ( T[Date]) )
VAR Fd = SELECTEDVALUE(Test[Fecha Detención])    
VAR Fr = SELECTEDVALUE(Test[Fecha Inicio]) 
VAR TimeDown = 
         IF(Fd<=Fi&&Fi<Fr&&Ft>Fr;24*(Fr-Fi);
         IF(Fd>=Fi&&Ft>Fd&&Fr>Ft;24*(Ft-Fd);
         IF(Fd>=Fi&&Ft>Fr;24*(Fr-Fd);
         0)))
         return TimeDown

 

And to get the N the code is:

FallaEnPeriodo = 
VAR Fi = CALCULATE ( MIN ( T[Date] ); ALLSELECTED ( T[Date]) )
VAR Ft = CALCULATE ( MAX ( T[Date] ); ALLSELECTED ( T[Date]) )
VAR Fd = SELECTEDVALUE(Test[Fecha Detención])    
VAR Fr = SELECTEDVALUE(Test[Fecha Inicio]) 
VAR FailuresInperiod = 
         IF(Fd<=Fi&&Fi<Fr&&Ft>Fr;1;
         IF(Fd>=Fi&&Ft>Fd&&Fr>Ft;1;
         IF(Fd>=Fi&&Ft>Fr;1;
         0)))
         return FailuresInperiod

that code is use to mark if is or no a fail in the period, the total amount of failures in the period is: 

Fallas = sumx(test;[FallaEnPeriodo])

 

And finally to get MTBF:

MTBF = IFERROR(((([Op. Time])-sumx(Test;[Time Down]))/[Fallas]);0)

 

That the way that i figure out,  there may be more efficent ways to do.