topic Re: Calculate time by day in DAX Commands and Tips

Calculate time by day

Lucky_BI — Tue, 21 Mar 2023 03:40:37 GMT

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

Re: Calculate time by day

tamerj1 — Tue, 21 Mar 2023 04:15:07 GMT

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 ) )

Re: Calculate time by day

Lucky_BI — Tue, 21 Mar 2023 04:37:52 GMT

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?

Re: Calculate time by day

Lucky_BI — Tue, 21 Mar 2023 04:56:31 GMT

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

Re: Calculate time by day

tamerj1 — Tue, 21 Mar 2023 05:36:03 GMT

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.