Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Start Date | End date | downtime |
08-06-201408:55:35 | 08-06-201409:55:35 | |
08-06-201413:55:35 | 08-06-201413:55:35 | 4 |
I want to calculate downtime in hrs based on the end date of first row and start date of the second row, also at the start of everyday downtime value should be empty
Solved! Go to Solution.
Hi @ganapathya,
You can try to use below formula:
down time = DATEDIFF(MAXX(FILTER(ALL(Sheet2),[ID]=EARLIER(Sheet2[ID])&&[End date]<EARLIER(Sheet2[Start Date])),[End date]),[Start Date],HOUR)+0
Regards,
Xiaoxin Sheng
Hi @ganapathya,
I'd like to suggest you use power query to add a index column, then use this index column to calculate time diff.
Calculate column:
down time = DATEDIFF(MAXX(FILTER(ALL(Sheet6),[End date]<EARLIER(Sheet6[Start Date])),[End date]),[Start Date],HOUR)+0
Regards,
Xiaoxin Sheng
ID | Start Date | End date | downtime |
Machine 1 | 08-06-201408:55:35 | 08-06-201409:55:35 | 4 |
Machine 1 | 08-06-201413:55:35 | 08-06-201415:55:35 | 1 |
Machine 1 | 08-06-201416:55:35 | 08-06-201417:55:35 | 2 |
Machine 1 | 08-06-201419:55:35 | 08-06-201423:55:35 | 0 |
Machine 2 | 09-06-201400:30:00 | 09-06-201401:30:00 | 2 |
Machine 2 | 09-06-201403:30:00 | 09-06-201405:30:00 | 2 |
Machine 2 | 09-06-201407:30:00 | 09-06-201423:59:59 | 0 |
For this above table ,same Query will work or some change is required please help me
No sir, it is not working downtime has showing different data, please check the below data
provide the changes accordingly
Chamber | Starttime | endtime | downtime |
CH1 | 4/25/2016 9:00:11 pm | 4/26/2016 7:58:53 am | 0 |
CH1 | 4/26/2016 8:28:21 Am | 4/26/2016 5:44:19 Pm | endtime of row 1-starttime of row 2 |
CH1 | 4/26/2016 6:37:08 Am | 4/27/2016 3:25:36 am | endtime of row 2 -starttime of row3 |
CH1 | 4/27/2016 4:03:24 am | 4/27/2016 12:57:32 am | endtime of row 3-starttime of row 4 |
CH1 | 4/27/2016 5:29:00 pm | 4/28/2016 2:44:38 am | endtime of row 4 -starttime of row5 |
ch1 | 4/28/2016 12:09:09pm | 4/28/2016 8:36:55pm | endtime of row 5 -starttime of row 6 |
Hi @ganapathya,
You can try to use below formula:
down time = DATEDIFF(MAXX(FILTER(ALL(Sheet2),[ID]=EARLIER(Sheet2[ID])&&[End date]<EARLIER(Sheet2[Start Date])),[End date]),[Start Date],HOUR)+0
Regards,
Xiaoxin Sheng
For example, i have date with time, same date ch1 start time and end time consider runs from 6 AM to 8.15 AM,then again from 10 AM to 11 pm...Now the downtime is 1.45 hrs...from 8.15 AM (End time of row 1) to 10 AM (Start Time Row 2)....
Hi @ganapathya,
Please double check your sample data if it contains the wrong records:
After I modify the highlight part, the formula works well.
Regards,
Xiaoxin Sheng