Unable to get correct sum for total working hours

[dudeeee]

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

[v-yadongf-msft · Accepted solution]

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.

[RichardOfSydney]

Sumx

[dudeeee]

can you elaborate in my problem's context?