TimeBetween function or similar?

[pawlowski6132]

So, I'm familiar with the DatesBetween function. I need similar results but, using Time.

For example, we have a use case wherein we have hours worked data for each hour of the day:

Start 2:00 PM..... 23 hours (multiple people working)

3:00 PM.....22 hours

4:00 PM.....24 hours

5:00 PM.....21 hours

6:00 PM.....20 hours

7:00 PM.....24 hours

8:00 PM.....22 hours

We have periodic "check-ins" where we want to know how many hours were worked in prior to that check in:

4:00 PM Check in = 45 hours worked in the last two hours from 2:00 PM to 4:00PM (23 + 22)

7:00 PM Check in = 65 hours worked in the last three hours from 4:00PM to 7:00PM (24+21+20)

etc.

I understand there isn't a TimeBetween function but, am stumped on how to even approach this.

Thanx in advance for your help.

[pawlowski6132]

Thank you so much for taking the time to reply.

Could you please elaborate a bit more on how this concept can calculate sum of hours between two time periods (regardless of date, we are always only looking at today).

thank you again

[Radhika2605]

Hi @pawlowski6132 

The expression in the filter context is checking for each row all the dates which are less than or equal to the time in the current context. Say we are at 4pm right now - so the filter will check the time in the first row time (2pm) and compare it with 4 pm as it is less then it will take this. then it will move to the second row and compare the time and keep on adding the values...

Side note - If you have both am as well as pm values then I would suggest using 24 hr time period instead of 12 hr for easier calculation.

Hope this clarifies.

Thanks

Radhika 

[Radhika2605]

Hi @pawlowski6132 ,

I think you can utilize the cumulative total concept. 

Total Hours = Sum( [Hours] ) 

Calculate([Total Hours],
Filter( 'Table', Table[Time] < Max( Table[Time] ) ) )

So this will give you a total of how many hours are worked. You can also add a filter for the day like - 

Calculate([Total Hours],
Filter( 'Table', Table[Time] < Max( Table[Time] ) ,
Filter ('Table', [Date] = Max([Date]) ) ) )

Hope this helps. 

Thanks

Radhika