cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Junaid11
Helper V
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.

EmployeeDateArrival timeDeparture time
A25/01/20229:00:00 AM11:03:00 AM
A25/01/202212:15:00 PM1:55:00 PM
A25/01/20223:55:00 PM8:44:00 PM
A26/01/202212:48:00 AM3:23:00 AM
A26/01/20224:48:00 AM6:00:00 AM
A27/01/20223:00:00 PM4:55:00 PM
A27/01/20226:38:00 PM7:12:00 PM
A27/01/20228:00:00 AM9:15:00 PM

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

EmployeeDateTime for each day
A25/01/202211:44:00
A26/01/202205:12:00
A27/01/202206:15:00
   
Total 23:11:00

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Junaid11 

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 =
    ADDCOLUMNS (
        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:

jdbuchanan71_0-1643486462062.png

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

 

 

 

View solution in original post

1 REPLY 1
jdbuchanan71
Super User
Super User

@Junaid11 

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 =
    ADDCOLUMNS (
        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:

jdbuchanan71_0-1643486462062.png

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

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.