Getting the date that has most of the work shift

[MBisceglia]

Hi everyone!

I have a real problem here. I have a dataset with pieces of work that have 2 fields: "PunchInTime" and "PunchOutTime". My problem is that I need to group these by the date that has most of the work shift. For instance, if an employee started working on 8/13 at 9 PM until 8/14 6 AM is going to be 8/14 instead of 8/13 since 6 hours of the shift falls into 8/14 (Please look below). I would like to add this as a step in my query in the query editor. Does anyone know how to do this? 

Any help will be highly appreciated, Thanks!

Michelle

[ImkeF · Accepted solution]

Hi @MBisceglia ,
you can use Date.EndOfDay  function to calculate duration with regards to PunchInTime and StartOfDay accordingly.
If you need help with this, please provide sample data like described here: 

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[ImkeF · Accepted solution]

Hi @MBisceglia ,
you can use Date.EndOfDay  function to calculate duration with regards to PunchInTime and StartOfDay accordingly.
If you need help with this, please provide sample data like described here: 

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[MBisceglia · Accepted solution]

Hi ImkeF,

Here's a sample of my dataset. Sorry for the format but it won't let me reply if I include a table.

Date                  ID          PunchInDate     PunchOutDate      PunchInTime                      PunchOutTime 

8/13/2020       3580         8/14/2020         8/14/2020        8/14/2020 6:01:00 AM     8/14/2020 2:01:00 PM

8/13/2020       3580         8/13/2020         8/14/2020        8/13/2020 9:49:00 PM     8/14/2020 6:00:00 AM

I  ended up creating 2 Custom Columns using the following formulas:

Shift duration (PunchInTime):

Duration.Hours(Date.EndOfDay([PunchInTime])-[PunchInTime])

Shift duration (punchOutTime):

Duration.Hours([PunchOutTime]-Date.StartOfDay([PunchOutTime]))

And created a third one to  select the right date:

if [PunchInDate]<>[PunchOutDate] and
[#"Shift duration (PunchInTime)"]>[#"Shift duration (punchOutTime)"]
then [PunchInDate] else
if [PunchInDate]<>[PunchOutDate] and
[#"Shift duration (PunchInTime)"]<[#"Shift duration (punchOutTime)"]
then [PunchOutDate] else
[PunchInDate]