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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.