CODEWITHSUNDEEP
powerbidax
Sonali
Sonali

Reputation: 2283

Power BI - Conversion of seconds to hours in DAX

I have a table containing User with Seconds Spent.

I want to convert seconds into hours. And Show total hours in hh:mm format.

But the sum of seconds is 400020 which is equal to 111.116667 hour in this case my below formula is not working as expected.

RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

Taken this formula from Power BI Community

Upvotes: 0

Views: 3431

Answers (2)

TJ_
TJ_

Reputation: 647

INT(test[Test]/3600) 
& ":" 
& RIGHT("00" & INT((test[Test]/3600 - INT(test[Test]/3600)) * 60);2)

Proof:

Download sample PBIX

Upvotes: 0

Foxan Ng
Foxan Ng

Reputation: 7151

The RIGHT function is cutting your hour digits if there are more than 2 digits.

Based on your use case you can change it to

RIGHT ( "00" & INT ( TableName[Duration] / 3600 ), 3 )

if you have 3 digits for hour.

or you can just remove the RIGHT function for the hour part if you don't need the leading zero padding at all:

INT ( TableName[Duration] / 3600 )

Upvotes: 1

Related Questions