Forum Discussion
Working with time duration
I am trying to put together a Power BI report that displays/totals/averages length of phone calls that are in hh:mm:ss format (e.g. 00:05:25 is a five minute and 25 second phone call). Struggling a lot with using this format to represent a duration - any tips would be greatly appreciated.
I'm importing spreadsheets from a phone system's report module. Trying to get things like total talk time by adding up the durations. Or averaging the talk time for multiple days (rows below are for a single phone user over multiple days).
Hi Anonymous
First, you need to convert the hh:mm:ss formatted strings to a total number of seconds to make them easier to sum and average.
Seconds = HOUR([Time]) * 3600 + MINUTE([Time]) * 60 + SECOND([Time])For display purposes, you might want to convert these aggregated durations back to a hh:mm:ss format. You can create a measure to do this. Here's how to convert the total talk time back to a readable format:
TotalTalkTimeFormatted = VAR Total = SUM('Table'[Seconds]) RETURN FORMAT ( DIVIDE(Total, 86400), "hh:mm:ss" )This is the result you want
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous
First, you need to convert the hh:mm:ss formatted strings to a total number of seconds to make them easier to sum and average.
Seconds = HOUR([Time]) * 3600 + MINUTE([Time]) * 60 + SECOND([Time])For display purposes, you might want to convert these aggregated durations back to a hh:mm:ss format. You can create a measure to do this. Here's how to convert the total talk time back to a readable format:
TotalTalkTimeFormatted = VAR Total = SUM('Table'[Seconds]) RETURN FORMAT ( DIVIDE(Total, 86400), "hh:mm:ss" )This is the result you want
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Firstly - let me thank you very much for your reply! In theory this is exactly what I want to do - but I am running into some issues due to details I should have mentioned in my original post.
I am needing to import this Excel sheet into the DataVerse and then I am hooking the Power BI report into my Dataverse table. The issue I am running into now is that when I create a Dateverse table, the only options I have are to use Text or Date/Time format. I don't think Date/Time format will work because it truncates the number of seconds - which I need to have as discreet values because these calls are mostly 1-2 minutes with x amount of seconds, so the seconds are really valueable information. So I have been trying to import the columns into the dataverse as text and work with the data that way. The issue I'm coming into is that when I try to convert the text to a date/time or just a time, I am being told I need to switch to Import mode vs. Direct Query, which I don't think will work because I need to continue to import spreadsheets into this table and report on all historical data.
Do you have any suggestions on how to import this data into DataVerse (where there is no "Time" column type available - just date/time, which truncates my data) and somehow convert it so that I can use the formulas you previously provided? Right now the data is considered Text so I cannot use HOUR(), MINUTE(), SECOND().
Thanks again!