Calculate duration on start and end time with overlapping days

[Anonymous]

Hi all,

I have a problem with date ranges.

In my table each row contains an activity code as well as a start time and an end time in Date/Time/Timezone format like this:

Start time	End time	Activity
09.03.2018 23:30:00 +00:00	11.03.2018 04:00:00 +00:00	A

For the report I need to calculate the duration per Activity per day.

The result for activity A should be

09.03.2018   00:30

10.03.2018   24:00

11.03.2018   04:00

Is there a way I can do this without splitting the rows via the query editor? I am not very experienced with M...

[Greg_Deckler]

I feel like I am getting closer but not really sure. This will generate the desired table for an individual item.

Table = 
VAR startDateTime = MAX(IncidentHours[Start Time])
VAR startDate = DATE(YEAR(startDateTime),MONTH(startDateTime),DAY(startDateTime))
VAR endDateTime = MAX(IncidentHours[End Time])
VAR endDate = DATE(YEAR(endDateTime),MONTH(endDateTime),DAY(endDateTime))
VAR tmpCalendar = CALENDAR(startDateTime,endDateTime)
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"Minutes",IF(startDate=[Date],ABS(DATEDIFF([Date]+1,startDateTime,MINUTE)),
                                                        IF(endDate=[Date],ABS(DATEDIFF(endDateTime,[Date],MINUTE)),
                                                            24*60
                                                        )
                                                    ))
RETURN tmpCalendar1

However, I have a feeling that is not exactly what you want. The basic problem here is that you have to "invent" days basically for this incident. Therefore, you can't really tie a date table to it which makes it incredibly problematic to display. 

I almost feel like you really have to solve this problem by starting in Power Query (M) and generate a table that has a row by incident for every day that it exists. So, 

3/9/2018,A

3/10/2018,A

3/11/2018,A

3/10/2018,B

3/11/2018,B

3/12/2018,B

Then you could relate this to your incident table and things would be vastly easier. I'm just not certain DAX is suited for this one. Perhaps @ImkeF or @Vvelarde have an idea on how you could generate that kind of table in Power Query from the data table you have shown above.

[ImkeF]

You could do that like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNbDUNTIwtFAwMrYyNrAyMFDSAYkaGkJEDUyAQhBRR6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"End time" = _t, Activity = _t]),
    ChgType = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"End time", type datetime}, {"Activity", type text}}),
    ListOfDates = Table.AddColumn(ChgType, "Dates", each List.Transform({Number.From(Date.From([Start time]))..Number.From(Date.From([End time]))}, Date.From)),
    ListOfDurations = Table.AddColumn(ListOfDates, "Duration", each {DateTime.From([Dates]{1})-DateTime.From([Start time])} & // Duration part of first day
								     List.Repeat({#duration(1,0,0,0)}, List.Count([Dates])-2) & // 24 hours for each day in between
								     {DateTime.From([End time])-DateTime.From(List.Last([Dates]))}), // Duration part of last day
    Result = Table.AddColumn(ListOfDurations, "Result", each Table.FromColumns({[Dates],[Duration]}, {"Date", "Duration"}))
in
    Result

[Anonymous]

That looks great!

But it is not working if Start time and End time are on the same date... It leads to a count argument -1  

[Anonymous]

Hi Pierre-Se, can you please share your solution to this challenge? I have the exact requirements and have not been able to find a solution yet. 

[ImkeF · Accepted solution]

True, haven't though about that. So we have to add some conditions like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNTTUNTIwtFAwMLIyNrAyMFDSQRE1AQpBRB2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"End time" = _t, Activity = _t]),
    ChgType = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"End time", type datetime}, {"Activity", type text}}),
    ListOfDates = Table.AddColumn(ChgType, "Dates", each List.Transform({Number.From(Date.From([Start time]))..Number.From(Date.From([End time]))}, Date.From)),
    ListOfDurations = Table.AddColumn(ListOfDates, "Duration", each if Date.From([Start time]) = Date.From([End time]) then {[End time]-DateTime.From([Start time])} else {DateTime.From(Date.AddDays([Dates]{0},1))-DateTime.From([Start time])} & 
								     (try List.Repeat({#duration(1,0,0,0)}, List.Count([Dates])-2) otherwise {}) & 
  							             {DateTime.From([End time])-DateTime.From(List.Last([Dates]))}), // Duration part of last day
    Result = Table.AddColumn(ListOfDurations, "Result", each Table.FromColumns({[Dates],[Duration]}, {"Date", "Duration"})),
    #"Expanded Result" = Table.ExpandTableColumn(Result, "Result", {"Date", "Duration"}, {"Result.Date", "Result.Duration"})
in
    #"Expanded Result"

[Anonymous]

Thanks! I just solved it on my own with the same condition 

I will now run it with my DB and see if everything works there as well.

[Greg_Deckler]

This is as far as I have gotten on this one. Will tinker with it some more maybe:

Create these four columns:

StartDate = [Start Time].[Date]

EndDate = [End Time].[Date]

StartDiff = ABS(DATEDIFF([StartDate]+1,IncidentHours[Start Time],MINUTE))

EndDiff = ABS(DATEDIFF([EndDate],IncidentHours[End Time],MINUTE))

You can create this measure for days that are "skipped" provided you have an unrelated Calendar table:

Hours = 
VAR myStart = MIN('Calendar'[Date])
VAR myEnd = MAX('Calendar'[Date]) + 1
VAR myEndStart = MAX('Calendar'[Date])
VAR OpenAllDay = COUNTROWS(FILTER(ALL(IncidentHours),([Start Time]<myStart&&[End Time]>myEnd)))
RETURN IF(OpenAllDay,24*60,0)

Everything is returned in minute which you would have to convert to duration format using something like this:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Haven't figured out a way to put it all together yet.