cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Pierre-Se Frequent Visitor
Frequent Visitor

Calculate duration on start and end time with overlapping days

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Calculate duration on start and end time with overlapping days

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"

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Calculate duration on start and end time with overlapping days

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.

 

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Calculate duration on start and end time with overlapping days

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.

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User III
Super User III

Re: Calculate duration on start and end time with overlapping days

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

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Pierre-Se Frequent Visitor
Frequent Visitor

Re: Calculate duration on start and end time with overlapping days

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  Smiley Sad

 

Super User III
Super User III

Re: Calculate duration on start and end time with overlapping days

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"

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Pierre-Se Frequent Visitor
Frequent Visitor

Re: Calculate duration on start and end time with overlapping days

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

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors