How to convert seconds into hh:mm format in Power Bi?

user_6803934 0
Viewed 22829

In Power Bi, I have a table that contains Name and TimeSpent by user in seconds. I want to convert total seconds spent by all users into duration format (hh:mm)

When I am getting seconds in hh:mm format for each user from database query, the values are coming up like these 12:63 etc. After importing these values into power bi, I tried to set its datatype to DateTime format but power bi shows an error saying that it is not a valid value. If I set the datatype of the column as string then strings dont add up.

What can be the ideal way to do it?

dax powerbi date datetime
6 Answers

you can solve this in one line:

measure = FORMAT(TIME(0;0;tableNAME[your_column]);"HH:mm:ss")
user_18863210

DAX code:

 = TIME(0,0,SUM('Table'[Timespent]))

Then click the modelling tab and choose Format - Date Time and choose the appropriate format.

user_45972030

I wanted a Power BI Measure wich is easy to read for this problem, code below if it's of use.

HH:MM = 
VAR TotalDuration = SUM(tableNAME[your_column] ) //if you use a measure just leave the SUM part out
VAR TotalHours = TRUNC (TotalDuration/3600)
VAR Min_ =  FORMAT(TRUNC(TotalDuration - TotalHours * 3600),"00")
RETURN
    TotalHours & ":" & Min_

The solution is adopted from the top answer of this question PowerBi Duration calculation in hh:mm:ss

user_165437870
Related