Forum Discussion
Solved
How to split total elapsed time into two rows when it carries over into two dates
Hello! I am trying to measure the daily downtime of a system to later create metrics on total downtime minutes for the month, average downtime minutes, etc (per system). The challenge that I am ha...
To do this in PowerQuery, I would create a duplicate of this query for the portion of the events that carry over to the next day, and append this query to the original, with the start and end dates of both the portions of two-day events adjusted to just their day's portion. This assumes you can't have events that can't cross over into a third day.
Here's how I would do this:
- Duplicate the query by right-clicking it in the Queries pane and selecting Duplicate.
In the duplicate query:- Filter the query to only those where the start date is on a different date to the end date, for example with a filter on
DateTime.Date([StartDateTime_Out]) <> DateTime.Date([EndDateTime_Out]) - Add a custom column that gets 12AM on the end date, for example with
#datetime(Date.Year([EndDateTime_Out]),Date.Month([EndDateTime_Out]), Date.Day([EndDateTime_Out]), 0, 0, 0) - Remove the current StartDateTime_Out and rename the new column to StartDateTime_Out
- Remove ElapsedTime(Mins) and add a replacement with the adjusted time. For example
Duration.TotalMinutes([EndDateTime_Out] - [StartDateTime_Out], Int64.Type) - Right-click this duplicate query in the Queries pane in PowerQuery, and uncheck Enable load so that this table doesn't show in your Power BI data model.
- Filter the query to only those where the start date is on a different date to the end date, for example with a filter on
- Modify your original query to make it represent the "start date" portion of multi-day rows:
- Add a conditional column that returns EndDateTime_Out if the start date and end date are the same, or else a new datetime with 23:59:59 on the end
#datetime(Date.Year([StartDateTime_Out]),Date.Month([StartDateTime_Out]), Date.Day([StartDateTime_Out]), 23, 59, 59) - Add a step that removes EndDateTime_Out, and another that renames the new column to EndDateTime_Out.
- Replace ElapsedTime(Mins) with a calculation that works out the total minutes between the new end datetime (... 23:59:59) and start date time.
- Add a conditional column that returns EndDateTime_Out if the start date and end date are the same, or else a new datetime with 23:59:59 on the end
- On the original query, append the query we created with the end-date records
- Duplicate the query by right-clicking it in the Queries pane and selecting Duplicate.
To do this in PowerQuery, I would create a duplicate of this query for the portion of the events that carry over to the next day, and append this query to the original, with the start and end dates of both the portions of two-day events adjusted to just their day's portion. This assumes you can't have events that can't cross over into a third day.
Here's how I would do this:
- Duplicate the query by right-clicking it in the Queries pane and selecting Duplicate.
In the duplicate query:- Filter the query to only those where the start date is on a different date to the end date, for example with a filter on
DateTime.Date([StartDateTime_Out]) <> DateTime.Date([EndDateTime_Out]) - Add a custom column that gets 12AM on the end date, for example with
#datetime(Date.Year([EndDateTime_Out]),Date.Month([EndDateTime_Out]), Date.Day([EndDateTime_Out]), 0, 0, 0) - Remove the current StartDateTime_Out and rename the new column to StartDateTime_Out
- Remove ElapsedTime(Mins) and add a replacement with the adjusted time. For example
Duration.TotalMinutes([EndDateTime_Out] - [StartDateTime_Out], Int64.Type) - Right-click this duplicate query in the Queries pane in PowerQuery, and uncheck Enable load so that this table doesn't show in your Power BI data model.
- Filter the query to only those where the start date is on a different date to the end date, for example with a filter on
- Modify your original query to make it represent the "start date" portion of multi-day rows:
- Add a conditional column that returns EndDateTime_Out if the start date and end date are the same, or else a new datetime with 23:59:59 on the end
#datetime(Date.Year([StartDateTime_Out]),Date.Month([StartDateTime_Out]), Date.Day([StartDateTime_Out]), 23, 59, 59) - Add a step that removes EndDateTime_Out, and another that renames the new column to EndDateTime_Out.
- Replace ElapsedTime(Mins) with a calculation that works out the total minutes between the new end datetime (... 23:59:59) and start date time.
- Add a conditional column that returns EndDateTime_Out if the start date and end date are the same, or else a new datetime with 23:59:59 on the end
- On the original query, append the query we created with the end-date records