topic Re: Unable to get correct sum for total working hours in DAX Commands and Tips

Unable to get correct sum for total working hours

dudeeee — Thu, 13 Oct 2022 06:58:44 GMT

Hello,
I am trying to calculate the total working hours from the given table between the date and time range. For each day I am getting the correct hours by subtracting two timestamps. But in the case of the total, the subtraction yields the hours between the two date ranges which is not what I need.
I want the sum of the total working hours which is the correct result. How can I achieve this and what am I doing wrong?
Here is the power bi visual table showing the data.

The measure formula I have used :

Total Working Hours = DATEDIFF([Lift First],[Lift Last],HOUR)

Here First Operation and Last Operation are both measures that gives min and max timestamp respectively.

Year	Month	Day	First Operation	Last Operation	Total working hours
2022	September	5	9/5/2022 6:00:04 AM	9/5/2022 1:06:02 PM	7
2022	September	11	9/11/2022 5:07:46 AM	9/11/2022 1:12:36 PM	8
Total			9/5/2022 6:00:04 AM	9/11/2022 1:12:36 PM	151

Re: Unable to get correct sum for total working hours

RichardOfSydney — Thu, 13 Oct 2022 06:38:21 GMT

Sumx

Re: Unable to get correct sum for total working hours

dudeeee — Thu, 13 Oct 2022 07:26:35 GMT

can you elaborate in my problem's context?

Re: Unable to get correct sum for total working hours

v-yadongf-msft — Fri, 04 Nov 2022 08:54:39 GMT

Hi dudeeee ,

This is my test table:

Please try following DAX to create measures:

First Operation = MINX(FILTER(ALL('Table'),'Table'[Day] = SELECTEDVALUE('Table'[Day])),'Table'[Operation]) Last Operation = MAXX(FILTER('Table','Table'[Day] = SELECTEDVALUE('Table'[Day])),'Table'[Operation]) Hours = IF(ISINSCOPE('Table'[Year]),DATEDIFF([First Operation],[Last Operation],HOUR),SUMX('Table',DATEDIFF([First Operation],[Last Operation],HOUR)))

The result you want:

Best regards,

Yadong Fang

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