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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ThuJa23
Advocate II
Advocate II

Calculate working hours per day with check in and check out time

Hi all, 

 

I have working hours data for 1 employee as below

with TerminalereignistypID is 1: check in, and 2: check out

The target working hours per day is 8:45

The break time is always fixed 45 minutes

 

I would like to calculate on the same day, how many hours an employee works by taking time from TerminalereignistypID  2 minus TerminalereignistypID  1 and minus also the fixed break time (0,45)

There are also some days, that the employee check out in the middle of day, and check in again as example from date 16.11.2022, if the break time between check out and check in again is greater than (>) 45 mins, then no more minus 0,45, but if it less than 45 mins, then minus also the rest, for ex: if self - break time is 30 mins, so in calculation of working hours just needs to minus more 15 mins (to reach total 45mins of break time).

In the example of 16.11.2022, from first check out to check in again took 1,50, it overs than 0,45, so in this case just calculate the sum of slot 1 working time (6:33 - 11:34) 5,01 and slot 2 (13:25 - 15:40) 2,15, sum of working hours this day is 7,16 (without minus 45 mins break time)

ThuJa23_2-1669726251252.png

 

Please help me how should I do the DAX measure in this case to calculate the working hours rightly?

 

Thanks so much for your helps

Thu

 

 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @ThuJa23 ,

 

Based on your description, I have created a simple sample:

vjianbolimsft_0-1669785596480.png

Please try:

Measure =
VAR _a =
    SUMX (
        'Table',
        IF (
            [TerminalereignistypID] = 2,
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        'Table',
                        [TerminalereignistypID] = 1
                            && [Time] < EARLIER ( 'Table'[Time] )
                            && [Date] = EARLIER ( 'Table'[Date] )
                    )
                ),
                [Time],
                MINUTE
            )
        )
    )
VAR _b =
    SUMX (
        'Table',
        IF (
            [TerminalereignistypID] = 1,
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        'Table',
                        [Date] = EARLIER ( 'Table'[Date] )
                            && [TerminalereignistypID] = 2
                            && [Time] < EARLIER ( 'Table'[Time] )
                    )
                ),
                [Time],
                MINUTE
            )
        )
    )
VAR _c =
    IF ( _b < 45, 45, 0 )
VAR _d = ( _a - _c ) / 60
RETURN
    INT ( _d )
        + 0.01
            * MOD ( _a - _c, 60 )

Final output:

vjianbolimsft_1-1669785673152.png

Best Regards,

Jianbo Li

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

 

 

View solution in original post

2 REPLIES 2
v-jianboli-msft
Community Support
Community Support

Hi @ThuJa23 ,

 

Based on your description, I have created a simple sample:

vjianbolimsft_0-1669785596480.png

Please try:

Measure =
VAR _a =
    SUMX (
        'Table',
        IF (
            [TerminalereignistypID] = 2,
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        'Table',
                        [TerminalereignistypID] = 1
                            && [Time] < EARLIER ( 'Table'[Time] )
                            && [Date] = EARLIER ( 'Table'[Date] )
                    )
                ),
                [Time],
                MINUTE
            )
        )
    )
VAR _b =
    SUMX (
        'Table',
        IF (
            [TerminalereignistypID] = 1,
            DATEDIFF (
                CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        'Table',
                        [Date] = EARLIER ( 'Table'[Date] )
                            && [TerminalereignistypID] = 2
                            && [Time] < EARLIER ( 'Table'[Time] )
                    )
                ),
                [Time],
                MINUTE
            )
        )
    )
VAR _c =
    IF ( _b < 45, 45, 0 )
VAR _d = ( _a - _c ) / 60
RETURN
    INT ( _d )
        + 0.01
            * MOD ( _a - _c, 60 )

Final output:

vjianbolimsft_1-1669785673152.png

Best Regards,

Jianbo Li

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

 

 

Hi @v-jianboli-msft ,

 

So sorry for my late replies. 

It works well, just we have more conditions in breaking time for employees. 

For example:

- if the employees work more or equal than 6 hours, then just minus 30 mins for lunch break

- if the employees work more than 9 hours, then minus 45 mins (included breakfast break 15mins, and lunch break 45 mins)

I am trying to test more with the calculation based on your formular. 

But thanks so much for your helps, it helps me to understand the structure defintely better 

 

Best regards,

Thu

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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