cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION

Accepted Solutions
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!

4 REPLIES 4
Community Support Team

## Re: Splitting duration between time frames

Hi @JRe5777 ,

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.
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º Trip Departure Time Fram Departure Arrival Time frame arrival Kms Trip Duration Duration in hours 12 1 18/01/2017 20:45 06:00 - 20:59 18/01/2017 21:03 21:00 - 23:59 2.4 00:18:00 0.30 12 2 18/01/2017 20:51 06:00 - 20:59 18/01/2017 20:52 06:00 - 20:59 2.4 00:19:00 0.02 Desired Visual Result (Matrix) in minutes of duration Trip 06:00 - 20:59 21:00 - 23:59 1 15 3 2 19 0
Community Support Team

## Re: Splitting duration between time frames

Hi @JRe5777 ,

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

Announcements

#### 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?

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

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