Finding the total hours ever day

[Viswajth_Nair95]

i have production data coming in like this, for each day. i have to calculate the number of production hours for each day in a seperate coulumn. since i am a beginner, i tried out some things but couldnt work it out.  Please help?

[Viswajth_Nair95]

Thank you @Samarth_18 , but it didnt fully work on a large data set somehow. i ended up getting total hours in the last 3 days o the year and blank in the other

Maybe it might be because i made some mistakes in the formula.

the name of the table is Query1, the date column is Date and Time column Time. I put in the following formula:

Measure=SUMX ( 'Query1', HOUR ( 'Query1'[Time] ) )

+ TRUNC ( SUMX ( 'Query1', MINUTE ( 'Query1'[Time] ) ) / 60 ) & ":"

& MOD ( SUMX ( 'Query1', MINUTE ( 'Query1'[Time] ) ), 60 ) & ":00"

[v-rzhou-msft · Accepted solution]

Hi @Viswajth_Nair95 ,

If Hour part and Mintue part in your code return blank, you will get result"::00". I suggest you to update your code as below. If there is no working hours in that day, your measure will return "00:00:00".

Measure = 
VAR _HOUR = 
SUMX ( 'Query1', HOUR ( 'Query1'[Time] ) )
    + TRUNC ( SUMX ( 'Query1', MINUTE ( 'Query1'[Time] ) ) / 60 ) 
VAR _MINUTE = MOD ( SUMX ( 'Query1', MINUTE ( 'Query1'[Time] ) ), 60 )
RETURN
IF(_HOUR = 0,"00",_HOUR)& ":"
& IF(_MINUTE = 0,"00",_MINUTE) & ":00"

Best Regards,
Rico Zhou

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

[Samarth_18]

@Viswajth_Nair95 Is it possible for you to share the PBIX file after removing sensitive data?

[Samarth_18]

Hi @Viswajth_Nair95 

You could create a measure as below:-

Measure =
SUMX ( 'table', HOUR ( 'Table'[hours] ) )
    + TRUNC ( SUMX ( 'table', MINUTE ( 'Table'[hours] ) ) / 60 ) & ":"
    & MOD ( SUMX ( 'table', MINUTE ( 'Table'[hours] ) ), 60 ) & ":00"

Dataset:-

Output:-

BR,

Samarth