Moving total working hours for contract employees on Trend Chart

[Souvik0812]

I have a dataset of Contract Employee's start and end date and time. The business hours to be considered as 06:00-14:00. I need to show the total business hours worked on a trend chart, showing daily, weekly or monthly view. It's ok just to consider the Hour, i.e. 07:45 to 14:00(Business hour finish time) can be counted as 7 hours. So, ideally for 01st March, it should show 27 hours. Breakdown as below:
8 hours for ID 01

8 hours fro ID 02

14-07 = 7 hours for ID 03

12-08 = 4 hours for ID 04

Employee ID	Start date & Time	End date & Time
01	27/02/2023 05:00	05/03/2023 09:00
02	28/02/2023 06:05	02/03/2023 14:30
03	01/03/2023 07:45	02/03/2023 09:45
04	01/03/2023 08:30	01/03/2023 12:45
05	02/03/2023 14:10	06/03/2023 10:30
06	02/03/2023 10:30	05/03/2023 08:30

There is also another huddle, Saturday and Sundays are holidays and so, shoudl always be counted as 0 hours. And so, 04th and 05th March should show 0 hours on the chart.

Please could you help with creating a measure to achieve this.

Thanks in advance.

[v-rzhou-msft · Accepted solution]

Hi @Souvik0812 ,

I suggest you to try code as below to create a measure.

DateDiff =
VAR _CURRENT =
    MAX ( 'Calendar'[Date] )
VAR _STEP1 =
    ADDCOLUMNS (
        'Table',
        "Start", DATEVALUE ( 'Table'[Start date & Time] ),
        "End", DATEVALUE ( 'Table'[End date & Time] )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "DateDiff",
            VAR _START =
                IF (
                    AND (
                        [Start date & Time]
                            <= _CURRENT + TIME ( 6, 0, 0 ),
                        [End date & Time]
                            >= _CURRENT + TIME ( 6, 0, 0 )
                    ),
                    _CURRENT + TIME ( 6, 0, 0 ),
                    IF ( [Start date & Time] < _CURRENT + TIME ( 14, 0, 0 ), [Start date & Time] )
                )
            VAR _END =
                IF (
                    AND (
                        [End date & Time]
                            >= _CURRENT + TIME ( 6, 0, 0 ),
                        [End date & Time]
                            <= _CURRENT + TIME ( 14, 0, 0 )
                    ),
                    [End date & Time],
                    IF (
                        AND (
                            [Start date & Time]
                                < _CURRENT + TIME ( 14, 0, 0 ),
                            [End date & Time]
                                >= _CURRENT + TIME ( 14, 0, 0 )
                        ),
                        _CURRENT + TIME ( 14, 0, 0 )
                    )
                )
            RETURN
                DATEDIFF ( _START, _END, HOUR )
    )
RETURN
    SUMX ( _STEP2, [DateDiff] )

Result is as below.

Best Regards,
Rico Zhou

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