Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Repair Date |
a | 10-09-2019 14:20 | 12-09-2019 15:30 |
a | 15-10-2019 10:32 | 20-10-2019 4:30 |
a | 11-11-2019 11:11 | 11-11-2019 12:30 |
b | 12-07-2019 12:45 | 14-07-2019 12:56 |
b | 18-09-2019 12:01 | 01-10-2019 0:00 |
b | 02-10-2019 12:34 | 10-10-2019 4:50 |
b | 12-11-2019 17:50 | 15-11-2019 0:00 |
If i take the following dates of evaluation :
Inicial Evaluation Date | 12-07-2019 |
Final Evaluation Date | 13-01-2020 |
The result will be:
Machine | Detention Date | Repair Date | Working time |
a | 10-09-2019 14:20 | 12-09-2019 15:30 | 1454 |
a | 15-10-2019 10:32 | 20-10-2019 4:30 | 787 |
a | 11-11-2019 11:11 | 11-11-2019 12:30 | 2034 |
b | 12-07-2019 12:45 | 14-07-2019 12:56 | 13 |
b | 18-09-2019 12:01 | 01-10-2019 0:00 | 1583 |
b | 02-10-2019 12:34 | 10-10-2019 4:50 | 37 |
b | 12-11-2019 17:50 | 15-11-2019 0:00 | 2221 |
For machine a:
1454 = 24* ( 12-09-2019 15:30 - 10-09-2019 14:20)
787 = 24 * ( 15-10-2019 10:32:00 - 12-09-2019 15:30:00)
2034 = 24 * (11-11-2019 11:11 - 20-10-2019 4:30 +
13-01-2020 - 11-11-2019 12:30 )
Total Working Time =4.276, anda the same for machine b. Also it is importat to consider the cases that the initial date evaluation is greater than the first detention date or any of those cases. Im only interest to get the sum of hours in between thta period of time.
Any help is greatly welcome-
Solved! Go to Solution.
Hi @josevarelac
Is this what you want?
this is dynamic, please check.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @josevarelac
Is this what you want?
this is dynamic, please check.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@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.
@v-juanli-msft MAggi, first of all thank for your time a dedication on this problem. Your aproch is all most perfect, but there are some flaws, the Working Time for the machine A, assuming Initial Date= 7/12/2019 and Final Date = 13/1/2020, is 4.267 hours.
The calculation of that number is showned in this image:
And the logic of that number is the following:
The same criteria applies to the machine B, also remember that the Final Date can be in between to failures to the working time is 0.
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...
@Greg_Deckler thanks for the advice, I´ve already check your post, but you only consider the case of static evaluation dates. I need to check on different range of dates. That case is a little tricky cuz you have more cases to take care on.