How to split total elapsed time into two rows when it carries over into two dates

[mmunozjr]

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 having is when I encounter a situation like shown on record ID 3 and 7 when a system downtime duration carries into the next day. What I am trying to accomplish is to separate into two rows the different portions of the corresponding downtime minutes for each separate day for that same system. In essence, all the same column headings should be identical, with perhaps a new column that just identifies the corresponding date, not necessarily the time. This of course would be based on 1440 minutes in a day.

Thank you for your assistance!

[mmunozjr]

Thank you so much for your prompt and helpful solution. 

[andrewpirie · Accepted solution]

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.

• 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.
• On the original query, append the query we created with the end-date records