cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JRe5777 Regular Visitor
Regular Visitor

Splitting duration between time frames

Hello everybody,

 

I would really appreciate your help on this subject.

 

I have a table of bus trips, with 2 columns for start of route and end of route, so we can calculate how many minutes/hours the trip takes.

 

Then I have 3 timeframes (I grouped by start hour):

00:00 - 05:59

06:00 - 20:59

21:00 - 23:59

 

Imagine one of the trips starts at 20:30 and ends at 21:30.

 

I want to have a way of saying that 30 min were in the time frame "06:00 - 20:59" and the other 30 min on the time frame "21:00 - 23:59".

 

Do you have any tip on how to do that?

 

I've tried some hourly calculation, with the limit of the time frame and the start or end date, but then the hour is a number and the date is a date and it doesn't calculate.

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
JRe5777 Regular Visitor
Regular Visitor

Re: Splitting duration between time frames

Hello @v-piga-msft  sorry for the delay, in the meanwhile I developed a solution.

What I meant was, for the first line the column trip duration = 18 minutes
And the trip started before 21h, so there were 15 minutes e that time frame and 3 minutes in the next time frame.

The way I did it, I don't know if it's the simplest but was the one I could remember, was to create calculated columns.
First of all, on power query I extracted the hour of departure and arrival (2 columns) and grouped them in the time frames I mentioned (00:00-05:59 | 06:00-20:59 | 21:00-23:59).

Then there were some calculated columns because I also had to divide between days (so for example, a trip starting today before midnight and ending tomorrow dawn was going to be divided in 2 time frames and in 2 days)
The code it's a bit complex and specific do some of the data conditions but I will still add a link for a word document and state this as the solution https://1drv.ms/w/s!AkP8HOhtvBzT4jgnTlVSlwxnonPf?e=T276py
Also, the information had to be in hours (not in minutes) so I had to divide minutes by 60 and seconds by 60^2.
There are 7 calculated columns, more or less one per page.

But thank you very much for your attention!

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Splitting duration between time frames

Hi @JRe5777 ,

I still a little confused about your scenario.

If it is convenient, could you share your data sample as table format and your desired output so that I could understand your logic better.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JRe5777 Regular Visitor
Regular Visitor

Re: Splitting duration between time frames

Hello @v-piga-msft 

 

Thank you for your reply, here goes the example. Let me know if you understand it.

I want to know how much of the trip is in one timeframe and how much of the trip is in another one.

 

 

Bus NºTripDepartureTime Fram DepartureArrivalTime frame arrivalKmsTrip DurationDuration in hours
12118/01/2017 20:4506:00 - 20:5918/01/2017 21:0321:00 - 23:592.400:18:000.30
12218/01/2017 20:5106:00 - 20:5918/01/2017 20:5206:00 - 20:592.400:19:000.02
         
  Desired Visual Result (Matrix) in minutes of duration    
  Trip06:00 - 20:5921:00 - 23:59    
  1153    
  2190    
Community Support Team
Community Support Team

Re: Splitting duration between time frames

Hi @JRe5777 ,

Sorry I still have confused about your desired output.

If it is convenient, could you explain the logic of your output. Why the value is 15 duting 06:00 - 20:59 for Trip 1?

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JRe5777 Regular Visitor
Regular Visitor

Re: Splitting duration between time frames

Hello @v-piga-msft  sorry for the delay, in the meanwhile I developed a solution.

What I meant was, for the first line the column trip duration = 18 minutes
And the trip started before 21h, so there were 15 minutes e that time frame and 3 minutes in the next time frame.

The way I did it, I don't know if it's the simplest but was the one I could remember, was to create calculated columns.
First of all, on power query I extracted the hour of departure and arrival (2 columns) and grouped them in the time frames I mentioned (00:00-05:59 | 06:00-20:59 | 21:00-23:59).

Then there were some calculated columns because I also had to divide between days (so for example, a trip starting today before midnight and ending tomorrow dawn was going to be divided in 2 time frames and in 2 days)
The code it's a bit complex and specific do some of the data conditions but I will still add a link for a word document and state this as the solution https://1drv.ms/w/s!AkP8HOhtvBzT4jgnTlVSlwxnonPf?e=T276py
Also, the information had to be in hours (not in minutes) so I had to divide minutes by 60 and seconds by 60^2.
There are 7 calculated columns, more or less one per page.

But thank you very much for your attention!

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 146 members 1,612 guests
Please welcome our newest community members: