Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
josevarelac
Frequent Visitor

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:

MachineDetention DateRepair Date
a10-09-2019 14:2012-09-2019 15:30
a15-10-2019 10:3220-10-2019 4:30
a11-11-2019 11:1111-11-2019 12:30
b12-07-2019 12:4514-07-2019 12:56
b18-09-2019 12:0101-10-2019 0:00
b02-10-2019 12:3410-10-2019 4:50
b12-11-2019 17:5015-11-2019 0:00

 

If i take the following dates of evaluation :

Inicial Evaluation Date12-07-2019
Final Evaluation Date

13-01-2020

 

The result will be:

MachineDetention DateRepair DateWorking time
a10-09-2019 14:2012-09-2019 15:301454
a15-10-2019 10:3220-10-2019 4:30787
a11-11-2019 11:1111-11-2019 12:302034
b12-07-2019 12:4514-07-2019 12:5613
b18-09-2019 12:0101-10-2019 0:001583
b02-10-2019 12:3410-10-2019 4:5037
b12-11-2019 17:5015-11-2019 0:002221

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-

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @josevarelac 

Is this what you want?

this is dynamic, please check.

Capture11.JPG

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.

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @josevarelac 

Is this what you want?

this is dynamic, please check.

Capture11.JPG

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:

 

Captura.JPG

 

And the logic of that number is the following:

 

Example.JPG

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.

 

 

Greg_Deckler
Super User
Super User

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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors