Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pierre-Se
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

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"

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

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. 

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"

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Greg_Deckler
Super User
Super User

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.

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.