Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.