cancel
Showing results for
Did you mean:
Helper V

## Difference of time with sum

Hello,

I have employee table in which there is arrival and departure time which employees comes in and goes out is recorder multiples times. I want to sum up the first time they entered and last time they went out the difference of total hours from first to last. I used

Min(aarival time)- Max(departure time). I am getting result correct as diference but it is not showing sum in table as correct. I am attaching the some data along with outcome that I want to have. is there a way to get correct sum of min and max time for each day. I want to show for each day irs time to last time departure differece representing total time spent. Kindly help in thi regard.

 Employee Date Arrival time Departure time A 25/01/2022 9:00:00 AM 11:03:00 AM A 25/01/2022 12:15:00 PM 1:55:00 PM A 25/01/2022 3:55:00 PM 8:44:00 PM A 26/01/2022 12:48:00 AM 3:23:00 AM A 26/01/2022 4:48:00 AM 6:00:00 AM A 27/01/2022 3:00:00 PM 4:55:00 PM A 27/01/2022 6:38:00 PM 7:12:00 PM A 27/01/2022 8:00:00 AM 9:15:00 PM

The outcome I need is based on days with sum in total for all days selection.

 Employee Date Time for each day A 25/01/2022 11:44:00 A 26/01/2022 05:12:00 A 27/01/2022 06:15:00 Total 23:11:00

1 ACCEPTED SOLUTION
Super User

I have better luck working with formatted time when I start with the time in seconds.  This first measure will calculate that for each employee / day MIN(Arribval) to MAX(Departure):

``````Time in seconds =
VAR _EmployeesWithTimes =
SUMMARIZE ( 'Table', 'Table'[Employee], 'Table'[Date] ),
"@Start", CALCULATE ( MIN ( 'Table'[Arrival time] ) ),
"@End", CALCULATE ( MAX ( 'Table'[Departure time] ) )
)
RETURN
SUMX ( _EmployeesWithTimes, DATEDIFF ( [@Start], [@End], SECOND ) )``````

Then I use that in a measure that formats the results.

``````Time for each day =
VAR _Seconds = [Time in seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
IF (
NOT ISBLANK ( _Seconds ),
FORMAT ( _Hours, "00" ) & ":" &
FORMAT ( _RemainingMinutes, "00" ) & ":" &
FORMAT ( _RemainingSeconds, "00" )
)``````

I added a second user just for testing and the measure should work however you display it:

I have attached my sample file for you to look at.

Super User

I have better luck working with formatted time when I start with the time in seconds.  This first measure will calculate that for each employee / day MIN(Arribval) to MAX(Departure):

``````Time in seconds =
VAR _EmployeesWithTimes =
SUMMARIZE ( 'Table', 'Table'[Employee], 'Table'[Date] ),
"@Start", CALCULATE ( MIN ( 'Table'[Arrival time] ) ),
"@End", CALCULATE ( MAX ( 'Table'[Departure time] ) )
)
RETURN
SUMX ( _EmployeesWithTimes, DATEDIFF ( [@Start], [@End], SECOND ) )``````

Then I use that in a measure that formats the results.

``````Time for each day =
VAR _Seconds = [Time in seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
IF (
NOT ISBLANK ( _Seconds ),
FORMAT ( _Hours, "00" ) & ":" &
FORMAT ( _RemainingMinutes, "00" ) & ":" &
FORMAT ( _RemainingSeconds, "00" )
)``````

I added a second user just for testing and the measure should work however you display it:

I have attached my sample file for you to look at.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors