Time calculation based on second row

[sTaLa]

Hello,

I have around 10 machines in my shop. I need to know the total ammount of time the machines are running (need to know the down times). I have a column called "EnFonction". Basically all I need to know is the total ammount of time the value of "EnFonction" is "True".

In the table below, the total ammount of time would be:

(11:43:34-11:42:35) + (12:00:58-12:00:25).

In other words, my machine was down from 11:43:34 to 12:00:25.

The example below is somewhat simple as it is for only one machine. I guess I need to be able to do this math with one machine at first.

But as you can see the table is filtered on the column BonTravail (Work Order). There will be many other lines and my aim is to seperate every calculation based on the work order.

I could format the table differently: that table is created from a PLC I programmed myself so I have some freedom on that part.

Could someone help me with that?

I searched for a whole day but was not able to find a similar application.

Thanks!

[sTaLa]

Alright, I did some more work on it and I think I established a good strategy. I need to create a new table based on the initial table. That new table would have one column for the StartTime, and another for the StopTime. This would allow me to easily calculate the running time with DATEDIFF.

The definition of the StartTime would be:

---- For a specified machine,

       ----the first row where EnFonction = True after it was False.

The definition of StopTime would be:
---- For a specified machine,

      ---- after the last StartTime,

             ---- the last row where EnFonction = True

Then I could use that post to calculate my UpTime:

Mean Time Between Failure (MTBF) and Power BI 

Now I need to figure out the DAX to create my new table.

Based on the sample I shoed on December 11th, the newly created table will need to look like this one.

[sTaLa · Accepted solution]

I received help from Microsoft support team. I am really impressed with the quality of the service I received! Here's what Thanmayee came up with to help me.

First, create two new columns of Timestamps. These are filtered columns: one of them only contains timestamps where EnFonction was True; the other column only contains timestamps where EnFonction was False.

Then create a calculated column where that DAX syntax:

uptime(sec) = 
VAR temp =
    TOPN (
        1;
        FILTER (
            MachineRunning;
            MachineRunning[ID] = EARLIER (MachineRunning[ID])
                && MachineRunning[TimeTrue] < EARLIER (MachineRunning[TimeTrue])
        );
        [Timestamp]; DESC
    )
RETURN IF(MachineRunning[EnFunction]=FALSE(); 0;(DATEDIFF ( MINX ( temp; MachineRunning[TimeTrue]); MachineRunning[TimeTrue]; SECOND)))

In the example below, all the values are filter on column ID. This ID serves as a WorkOrder number instead of the NoBonTravail column I used in my OP.

[v-lid-msft]

Hi @sTaLa ,

Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, we have learned a lot from it.

Would you please kindly mark your shared solution as an answer so that it can benefit more users?

Best regards,

[amitchandak]

 If possible please share a sample pbix file after removing sensitive information.
Thanks

[sTaLa]

Sure!

See to that link: Prod_DB_BI_(local)_07 Share 

Thanks for your help!