Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors