Calculate time by day

[Lucky_BI]

How do I add up the duration (Time Check out - Time Check in) of each name based on the date of the day excluding him from a different outlet with DAX?

Example:

• Name : Mike
• Dates:
  • 20/03/2023
    • Outlet A: 17:00:00 - 08:00:00 = 09:00:00
    • Outlet B: 16:00:00 - 08:00:00 = 08:00:00
  • 21/03/2023
    • Outlet C: 16:30:00 - 08:00:00 = 08:30:00

So, Mike's Duration for 20/03/2023 is 17:00:00 Hours. Meanwhile, Mike's duration for 21/03/2023 is 08:30:00 Hours
Download file: Time.xlsx
Thank you for your help

[Lucky_BI]

Thank you for the suggestion @tamerj1 
But in this case, a worker has no more than 24 hours working time at each outlet for one day. So the total in one day will not be more than 24 hours. If so how is it?

[tamerj1]

@Lucky_BI 
Yes but if you aggregate the dates for each name it will exceed 24 hours. However you can just delete the CONVERT part from the code but note that the totals will be wrong.

[tamerj1 · Accepted solution]

Hi @Lucky_BI 
DateTime format cannot display times more than 24 hours. This has to be a decimal number or custom formatted string. Please refer to attached sample file with the proposed solution

Total Hours = 
SUMX ( 
    SUMMARIZE ( 
        'Table',
        'Table'[Name],
        'Table'[Date]
    ),
    CONVERT ( 
        SUMX ( 
            CALCULATETABLE ( 'Table' ),
            'Table'[Time Check Out] - 'Table'[Time Check In]
        ),
        DOUBLE
    )
)

[Lucky_BI]

I've found a solution, sir @tamerj1 
Thank you very much for your help