I want help setting up a data set so I can extract certain data. I want to be able to extract hours per day.
Basically I am trying to get a Active Time for a process on all days used and days not used. There are days when nothing happens and I want to account for that also.
This is a sample of my data set. I know I will need to add some columns to get the data I need etc.
| Server | JobName | CreateDate | CreateTime | StartDate | StartTime | EndDate | EndTime | QueueTime | RunTime | Status |
|---|---|---|---|---|---|---|---|---|---|---|
| DR-SIM-1 | 0401152430.bat | 4/1/2022 | 3:24:43 PM | 4/1/2022 | 10:11:26 PM | 4/2/2022 | 11:24:48 AM | 406 | 793 | Success |
| DR-SIM-1 | 0401154749.bat | 4/1/2022 | 3:48:03 PM | 4/2/2022 | 11:25:03 AM | 4/3/2022 | 12:49:29 AM | 1177 | 804 | Success |
| DR-SIM-1 | 0404193450.bat | 4/4/2022 | 7:35:22 PM | 4/4/2022 | 7:35:44 PM | 4/6/2022 | 11:55:44 PM | 0 | 3140 | Success |
I do have a separate Date table showing all available dates that will dynamically update.
I am stuck with how to to setup the data table to get this data so I can show on a dashboard.
This is what I want to output in a table visual in Power Bi. The hrs shown are not accurate. Just a representation of what I want.
| Date | Active (hrs) | Down Time (hrs) |
|---|---|---|
| 4/1/2022 | 2 | 22 |
| 4/2/2022 | 23.5 | 0.5 |
| 4/3/2022 | 1 | 23 |
| 4/4/2022 | 0.5 | 11.5 |
| 4/5/2022 | 24 | 0 |
| 4/6/2022 | 7.5 | 16.5 |
| 4/7/2022 | 0 | 24 |
I have tried using DATEDIFF but that only works if the start and end time is on the same day.
I want to be able to break it up into the individual days if the time spans multiple days.