calculate downtime

tomvermaas — Tue, 05 Sep 2023 19:14:11 GMT

I am looking for help.

I would like to calculate the downtime of a machine in a column of a table
for this I need the difference in minutes between the start time and stop time.
The only exception is that the machine produces on working days from Monday to Friday from 6am to 11pm.

so if the machine breaks down on Friday 10:50 PM and the fault is resolved on Monday 6:10 AM, 20 minutes of downtime must be calculated.

I have tried everything but I cannot find the right combination of formulas.

does anyone know if this is possible?

example:

Start: 9/1/2023 6:00:00 PM
Stop: 9/1/2023 8:13:00 PM
Result: 133

Start: 9/1/2023 9:15:00 PM
Stop: 9/2/2023 8:13:00 PM
Result: 105

Start: 9/3/2023 9:00:00 AM
Stop: 9/3/2023 8:30:00 PM
Result: 0

Start: 9/1/2023 10:15:00 PM
Stop: 9/4/2023 8:00:00 AM
Result: 165

Re: calculate downtime

lbendlin — Tue, 05 Sep 2023 17:33:38 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: calculate downtime

v-yueyunzh-msft — Wed, 06 Sep 2023 03:43:22 GMT

Hi , tomvermaas

According to your description, you want to calculate the downtime for your machine and you want to wxclude the Saturday and Sunday . And the working time is from 6am to 11pm.

Here are the steps you can refer to :
(1)This is my test data:

(2)We can click "New Column" and enter this:

Column 2 = var _start = [Start] var _stop = [Stop] var _Start_Time= TIMEVALUE("6:00:00") var _End_Time = TIMEVALUE( "23:00:00") var _one_Day_Start = DATEVALUE(_start)+_Start_Time var _one_Day_End = DATEVALUE(_start)+_End_Time VAR _DATE_TABLE = ADDCOLUMNS( CALENDAR(_start,_stop) , "WeekDay" , WEEKDAY([Date],2),"Start_Date_Time",[Date]+_Start_Time , "End_Date_Time" , [Date]+_End_Time) var _Date_count = COUNTROWS(_DATE_TABLE) var _Week_day = WEEKDAY(_start,2) var _last_date = MAXX(_DATE_TABLE,[Date]) var _first_date = MINX(_DATE_TABLE,[Date]) var _DATE_TABLE_2 = ADDCOLUMNS(_DATE_TABLE , "MIN" , SWITCH(TRUE(), [WeekDay] in {6,7} , 0 , [Date] = _last_date , IF( _stop<[Start_Date_Time] ,0 , IF( _stop>=[Start_Date_Time] && _stop<= [End_Date_Time] ,INT(HOUR(_stop-[Start_Date_Time]))*60+ MINUTE(_stop-[Start_Date_Time]), INT( HOUR(_End_Time-_Start_Time))*60)), [Date]=_first_date, IF(_start<[Start_Date_Time],INT( HOUR(_End_Time-_Start_Time))*60, IF(_start>=[Start_Date_Time] && _start<=[End_Date_Time], INT(HOUR([End_Date_Time]-_start))*60+MINUTE([End_Date_Time]-_start) ,0)), INT( HOUR(_End_Time-_Start_Time))*60 ) ) var _One_Day_Min = SWITCH(TRUE(), _stop<_one_Day_Start || _start>_one_Day_End,0, _start<_one_Day_Start && _stop>= _one_Day_Start && _stop<= _one_Day_End ,INT(HOUR(_stop-_one_Day_Start))*60+ MINUTE(_stop-_one_Day_Start), _start<_one_Day_Start && _stop>_one_Day_End , INT( HOUR(_End_Time-_Start_Time))*60, _start>=_one_Day_Start && _start<=_one_Day_End && _stop>= _one_Day_Start && _stop<= _one_Day_End , INT(HOUR(_stop-_start))*60+ MINUTE(_stop-_start), _start>=_one_Day_Start && _start<=_one_Day_End && _stop>_one_Day_End , INT(HOUR(_one_Day_End-_start))*60+ MINUTE(_one_Day_End-_start),INT( HOUR(_End_Time-_Start_Time))*60) return IF(_Date_count =1 &&_Week_day in {6,7} , 0 , IF(_Date_count=1 , _One_Day_Min, SUMX(_DATE_TABLE_2,[MIN]) ))

Then we can get the result as follows:

(3)If you want to create a measure , you can just modify the first two rows like this:

Measure = var _start = MAX('Table'[Start]) var _stop = MAX('Table'[Stop]) var _Start_Time= TIMEVALUE("6:00:00") var _End_Time = TIMEVALUE( "23:00:00") var _one_Day_Start = DATEVALUE(_start)+_Start_Time var _one_Day_End = DATEVALUE(_start)+_End_Time VAR _DATE_TABLE = ADDCOLUMNS( CALENDAR(_start,_stop) , "WeekDay" , WEEKDAY([Date],2),"Start_Date_Time",[Date]+_Start_Time , "End_Date_Time" , [Date]+_End_Time) var _Date_count = COUNTROWS(_DATE_TABLE) var _Week_day = WEEKDAY(_start,2) var _last_date = MAXX(_DATE_TABLE,[Date]) var _first_date = MINX(_DATE_TABLE,[Date]) var _DATE_TABLE_2 = ADDCOLUMNS(_DATE_TABLE , "MIN" , SWITCH(TRUE(), [WeekDay] in {6,7} , 0 , [Date] = _last_date , IF( _stop<[Start_Date_Time] ,0 , IF( _stop>=[Start_Date_Time] && _stop<= [End_Date_Time] ,INT(HOUR(_stop-[Start_Date_Time]))*60+ MINUTE(_stop-[Start_Date_Time]), INT( HOUR(_End_Time-_Start_Time))*60)), [Date]=_first_date, IF(_start<[Start_Date_Time],INT( HOUR(_End_Time-_Start_Time))*60, IF(_start>=[Start_Date_Time] && _start<=[End_Date_Time], INT(HOUR([End_Date_Time]-_start))*60+MINUTE([End_Date_Time]-_start) ,0)), INT( HOUR(_End_Time-_Start_Time))*60 ) ) var _One_Day_Min = SWITCH(TRUE(), _stop<_one_Day_Start || _start>_one_Day_End,0, _start<_one_Day_Start && _stop>= _one_Day_Start && _stop<= _one_Day_End ,INT(HOUR(_stop-_one_Day_Start))*60+ MINUTE(_stop-_one_Day_Start), _start<_one_Day_Start && _stop>_one_Day_End , INT( HOUR(_End_Time-_Start_Time))*60, _start>=_one_Day_Start && _start<=_one_Day_End && _stop>= _one_Day_Start && _stop<= _one_Day_End , INT(HOUR(_stop-_start))*60+ MINUTE(_stop-_start), _start>=_one_Day_Start && _start<=_one_Day_End && _stop>_one_Day_End , INT(HOUR(_one_Day_End-_start))*60+ MINUTE(_one_Day_End-_start),INT( HOUR(_End_Time-_Start_Time))*60) return IF(_Date_count =1 &&_Week_day in {6,7} , 0 , IF(_Date_count=1 , _One_Day_Min, SUMX(_DATE_TABLE_2,[MIN]) ))

Then we can also get the same value:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

topic Re: calculate downtime in DAX Commands and Tips

calculate downtime

Re: calculate downtime

Re: calculate downtime