Total worked Hours (measure)

[Anonymous]

Hi Experts

I am having a nightmare in trying to work out total worked hours based in the sample data for mulitple employees. In the smaple data the total worked hours for the month Nov 23 should be 267.17.00 in hours:minutes:sec

The date in the sample data is link to date in my calender table and i get 17:35:00 

Sample data

CRM	Date	Duration	Contracted/OT
Apples	24-Nov-23	04:17:00	OVERTM:
Apples	23-Nov-23	01:00:00	OVERTM:
Apples	20-Nov-23	01:00:00	OVERTM:
Apples	16-Nov-23	01:00:00	OVERTM:
Apples	13-Nov-23	01:00:00	OVERTM:
Apples	06-Nov-23	01:00:00	OVERTM:
Apples	02-Nov-23	01:00:00	OVERTM:
Apples	20-Nov-23	01:00:00	OVERTM:
Apples	16-Nov-23	01:00:00	OVERTM:
Apples	13-Nov-23	01:00:00	OVERTM:
Apples	06-Nov-23	01:00:00	OVERTM:
Apples	02-Nov-23	01:00:00	OVERTM:
Apples	25-Nov-23	07:00:00	Normal Hours
Apples	18-Nov-23	07:00:00	Normal Hours
Apples	11-Nov-23	07:00:00	Normal Hours
Apples	03-Nov-23	07:00:00	Normal Hours
Apples	18-Nov-23	07:00:00	Normal Hours
Apples	11-Nov-23	07:00:00	Normal Hours
Apples	03-Nov-23	07:00:00	Normal Hours
Apples	17-Nov-23	04:00:00	OVERTM:
Apples	17-Nov-23	04:00:00	OVERTM:
Apples	27-Nov-23	07:30:00	Normal Hours
Apples	23-Nov-23	07:30:00	Normal Hours
Apples	22-Nov-23	07:30:00	Normal Hours
Apples	21-Nov-23	07:30:00	Normal Hours
Apples	20-Nov-23	07:30:00	Normal Hours
Apples	16-Nov-23	07:30:00	Normal Hours
Apples	15-Nov-23	07:30:00	Normal Hours
Apples	14-Nov-23	07:30:00	Normal Hours
Apples	13-Nov-23	07:30:00	Normal Hours
Apples	09-Nov-23	07:30:00	Normal Hours
Apples	08-Nov-23	07:30:00	Normal Hours
Apples	07-Nov-23	07:30:00	Normal Hours
Apples	06-Nov-23	07:30:00	Normal Hours
Apples	02-Nov-23	07:30:00	Normal Hours
Apples	01-Nov-23	07:30:00	Normal Hours
Apples	20-Nov-23	07:30:00	Normal Hours
Apples	16-Nov-23	07:30:00	Normal Hours
Apples	15-Nov-23	07:30:00	Normal Hours
Apples	14-Nov-23	07:30:00	Normal Hours
Apples	13-Nov-23	07:30:00	Normal Hours
Apples	09-Nov-23	07:30:00	Normal Hours
Apples	08-Nov-23	07:30:00	Normal Hours
Apples	07-Nov-23	07:30:00	Normal Hours
Apples	06-Nov-23	07:30:00	Normal Hours
Apples	02-Nov-23	07:30:00	Normal Hours
Apples	01-Nov-23	07:30:00	Normal Hours

[v-xuxinyi-msft · Accepted solution]

Hi @Anonymous 

You can create a calculated column as follows.

Second =
HOUR ( 'Sample'[Duration] ) * 3600
    + MINUTE ( 'Sample'[Duration] ) * 60
    + SECOND ( 'Sample'[Duration] )

Then create a measure as follows.

total =
VAR _TotalSeconds =
    CALCULATE ( SUM ( 'Sample'[Second] ), ALL ( 'Sample' ) )
VAR _Hours =
    INT ( DIVIDE ( _TotalSeconds, 3600, 0 ) )
VAR _Minutes =
    MOD ( DIVIDE ( _TotalSeconds, 60, 0 ), 60 )
VAR _Seconds =
    MOD ( _TotalSeconds, 60 )
RETURN
    _Hours & ":" & _Minutes & ":" & _Seconds

Is this the result you expect?

Best Regards,

Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.