Amount of hour by interval from Start Time and end Time

[SpartakO1]

Hello everyone,

i am struggled to find a solution in power query to evaluate the amount of hours for intervals

Start Time	End Time	unique ID
6/20/2021 12:33:03 PM	6/20/2021 12:49:33 PM	1
6/20/2021 12:49:33 PM	6/20/2021 12:54:02 PM	2
6/20/2021 12:54:02 PM	6/20/2021 1:17:48 PM	3
6/20/2021 1:17:48 PM	6/20/2021 1:19:16 PM	4

the desired outcome will be

Interval	Amount of hrs
0:00
1:00
2:00
3:00
4:00
5:00
6:00
7:00
8:00
9:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00

Can anyone help me to get the best solution in m?

Thanks!

[v-henryk-mstf]

Hi @SpartakO1 ,

What's the meaning of getting the split of the hours by interval from the given "start time" and "end time". For example start time:12:54:02 PM end time:6/20/2021 1:17:48, so you want to get a column like below?

If this is the case, generally speaking, the second/minutes/hour difference of the two columns can be obtained, but it may not be possible to create a column to obtain the interval of the date difference through M.

If not, can you further describe your needs? It is best to provide screenshots of the expected results, I will answer you as soon as possible.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartakO1]

Hi Henry, thanks for your time!

For the start time:12:54:02 PM end time:6/20/2021 1:17:48

i will need to split in intervals like this:

12:00-13:00 -> 00:05:58

13:00-14:00 -> 00:17:48

let me know if now is more clear

Thank you!

[v-henryk-mstf]

Hi @SpartakO1 ,

You can create dax to judge the time interval according to the formula, but each column can only return one judgment value. If the interval between the start date and the end date exceeds two hours, then whether it is necessary to display the complete hour.

which result?

Looking forward to your reply.

Best Regards,
Henry

[SpartakO1]

Hi Henry, the left one is perfection!

thanks again!!

[v-henryk-mstf]

Hi @SpartakO1 ,

I probably understand your situation. Unfortunately, the latest version of powerbi cannot dynamically create such row results for the time being. Only the dax formula can be used to create a column that meets the conditions based on the time difference, but a column can only display one satisfied result.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartakO1]

Thanks a lot Henry for your time, I appreciate!!

Can you please specify better the Dax approach?

Thanks again

[watkinnc]

If you are asking how to make a table of hours, you could use:

= Table.FromRows(List.Times(#time(0,0,0), 24, #duration(0,1,0,0)))

--Nate

[SpartakO1]

Hello Nate and thanks for your time!

actually I'm trying to get the split of the hours by interval from the given "start time" and "end time"

it's a little bit hard to achieve, and I'm not confident enough in m, as you can see.

Thanks