topic Re: Calculate total hours worked in DAX Commands and Tips

Calculate total hours worked

RussHaight — Mon, 05 Feb 2024 15:23:14 GMT

This is from SQL backend.I have table named Punch. It has 1 field named TotalTimeHHMM in type text. I want to sum/add the TotalTimeHHMM. The values are in this format. 10:19 which reperesents 10 hours 19 minutes. This field also has leading 0's. an example would be 02:57 which represents 2 hours 57 minutes. I do not want to breakout in days, just hours and minutes. An example would be this-18:30 + 15.45 + 12.11 which would equal adding the hours of 18+15+12 for 45 and adding the minutes of 30+45+11 which would equal 86 minutes or 1 hour 26 minutes for a grand total of 46 hours and 26 minutes -45 hours + 1 hour 26 minutes.

Thank you in advance.

Re: Calculate total hours worked

RussHaight — Mon, 05 Feb 2024 20:19:37 GMT

I figured it out.

Instead of using the TotalTime column which was a conversion fromTotalMinutes column, I just used TotalMinutes column. Example as follows

TotalMinutes 598.8 (decimal) converts to TotalTime 09:59 (text) with 09 being hours and 59 being minutes. I created a new column with the following dax formula

Total Time =

VAR TotalMinutes = 'punch'[Total Minutes]

RETURN

FORMAT(QUOTIENT(TotalMinutes, 60), "0") & " hours " & FORMAT(MOD(TotalMinutes, 60), "0") & " minutes"

which gave me an output of 9 hours 59 minutes.

I created a measure using the same dax formula and was able to drop it on a card to get total hours worked in hours/minutes output. I then added 2 slicers, one for location and one for employee.

There might be a better way of doing this but this is what I did to get it to work.

Re: Calculate total hours worked

Anonymous — Tue, 06 Feb 2024 01:40:46 GMT

Hi RussHaight ,

Thank you for providing the solution.

If the problem has been solved, please mark it as solution. if there is still a problem, please point it out and I will answer it for you as soon as possible.

Best Regards,
Adamk Kong