Chelsie Eiden's Duration 09-16-2019 13:12 PM - last edited 09-17-2019 09:30 AM

[Greg_Deckler]

Chelsie Eiden is my new favorite human being on the face of the planet. I don't know her major but, even if she is majoring in math, it still wouldn't change my mind on this one. That's how much I like this individual. The reason she is my favorite human being on the face of the planet is because she has finally...FINALLY, solved a "problem" with Power BI that is, ohhhh, say at least 4 or 5 years old.  Since the dawn of Power BI there has been this problem with aggregating duration in HH : MM : SS format. You could convert it to seconds to aggregate it but you couldn't display it in the hours, minutes, seconds format in a visual that properly aggregated it in column charts because the minute you did a concatenation or a format on it, "POOF" it became text. Maddening!! I have been harping on this issue for, well, forever, such as in this post I did with @konstantinos ages ago.

So, Chelsie, thank-you, thank-you, thank-you from the bottom of my heart! I have named this new Quick Measure just for you.

Chelsie Eiden's Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = SUM([Duration])
// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
RETURN
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds

All but the last line is the code from that article that @konstantinos and I wrote years and years ago. The only difference is the last line. Once you have this measure, then all you have to do is implement Chelsie Eiden's Custom Format String with a value of "00:00:00" (no double quotes). Boom!!

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/#customForm...

eyJrIjoiYjE5ZDZkN2EtODdlNy00ZmUxLWIyOGItOWRhYjU0NDY2Y2VhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Kristoff15]

Hey All, Anyone knows why some of the data shows over a minute? 00:00:79 instead of 00:01:19?
My dax is a little bit modified. I needed the average duration on a monthly basis so I had a months as my X axis. ASADuration was no longer summed because I had another field convert my time column to seconds already. 

SpeedOfAnswerDuration =

    AVERAGEX(

        'call',

VAR ASADuration = 'call'[SpeedOfAnswerCol]

VAR Hours = INT ( ASADuration / 3600)

VAR Minutes = INT ( MOD( ASADuration - ( Hours * 3600 ),3600)/60)

VAR Seconds = ROUNDUP(MOD (MOD( ASADuration - ( Hours * 3600),3600),60),0)

RETURN

    Hours * 10000 + Minutes * 100 + Seconds

    )

[Greg_Deckler]

@Kristoff15 You need to compute the AVERAGE of 'call'[SpeedOfAnswerCol] and THEN convert it to duration.

[Kristoff15]

Simple yet effective solution! Thanks Greg!

[BeckiB]

Hi Greg (and others), 

Thank you so much for sharing this! It's been an absolute life saver when it comes to displaying durations for various measures I'm reporting on. Although I appreciate it was 2019!

However, I'm having an issue with one of my measures. 

They are input to PowerBI as hh:mm:ss and data type set as time; I'm then converting into seconds (when I update this, I'll do the conversion in SQL for ease). 
I'm then converting BACK to hh:mm:ss using Chelsie's duration custom format. This has been successful for all but one measure where I have an average of 00:04:96. 

HOWEVER: 

Since I'm converting to seconds within PBI I'm creating a "Seconds" measure, which I then can't calculate the SUM of to put into yours/Chelsie's formula. 

If I create the "Seconds" measure as a new column within the table, the conversion doesn't work. 

This has not been an issue with the other duration I'm calculating, where all ":ss" values are <60. Any ideas on where my issue is?

  

 Many thanks!

[PranamG]

For Average Wrap Duration, you need to divide Duration by Total Rows using CountRows function (COUNTROWS[Transferred Calls]) and then convert the result in terms of  Hours, Minutes and Seconds. Hope this helps!

[ARVINDlsllp]

Hello

Can anyone help me with  the DAX , I have created  a column in table  but total is not appearing  at the end i am using table visual .

Total =
VAR StartTime = ('All Measures'[Mea_New_Gen_Reg_Tim])
VAR EndTime = ('UN_ASYMAN_TRACK'[Released_Date_Time])
VAR Difference = EndTime - StartTime
VAR TotalHours = INT(Difference * 24)
VAR TotalMinutes = INT(MOD(Difference * 24 * 60, 60))
VAR TotalSeconds = INT(MOD(Difference * 24 * 60 * 60, 60))
RETURN
IF(Difference < 0, BLANK(), FORMAT(TotalHours, "00") & ":" & FORMAT(TotalMinutes, "00") & ":" & FORMAT(TotalSeconds, "00"))
 

[PranamG]

The total seems accurate. The date difference between the 2 dates seem to be 414 days and change hours. Did you want the total to appear as Days, Hours, Minutes and Seconds? If yes, please refer to below comment by nigeldavidmaxey on how to set this up to reflect days, hours, minutes and seconds.

[nigeldavidmaxey]

This was really useful for me to find. I wanted to display elapsed time in readable format in addition to raw value (in hours). I was therefore able to adapt this as shown below to get the desired format:

Formatted Elapsed Time = 

// We start with a duration in number of seconds
VAR Duration = SUM(JIRA_DATA_TEAMS[CALCULATED_ELAPSED_MINUTES]) * 60
// There are 86,400 seconds in an day
VAR Days = INT ( Duration / 86400)
// There are 3600 seconds in a hour
VAR Hours = INT ( MOD( Duration - ( Days * 86400 ),86400 ) / 3600)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Minutes = INT (MOD ( MOD( Duration - ( Days * 86400 ),86400 ), 3600 ) / 60)

VAR DaysText = IF(Days > 0, Days & "d ","")

VAR HoursText = IF(Hours > 0, Hours & "h ","")

VAR MinutesText = IF(Minutes > 0, Minutes & "m ","")

RETURN
// We put the hours, minutes and seconds into the proper "place"
// Days * 10000 + Hours * 100 + Minutes

DaysText & HoursText & MinutesText

[bgriff135]

@Greg_Deckler This is great, super helpful, thank you for sharing. I've found one issue though - if you use a legend in the colomn chart, the total values are not accurate. The Total becomes a sum of each part of the time calc and you can get minutes and seconds greater than 60 (ex from the provided file, the total becomes "114:77:61" instead of "115:18:01") .  Is there a way to structure this meausre so it displays correctly when a legend is added to the visualization? Screenshot below and pbix file attached. Thanks!

[Greg_Deckler]

@bgriff135 I am going to guess that what is going on is that you essentially have kind of a measures totals problem going on but may have to take a closer look. So, I'm wondering if you need to do this essentially: Quick Measure, Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[strifler]

Hi Greg!

Formatting the value works properly, but I think we cannot visualize it

because the 1:00:00 bar shouldn't be ~40% larger than the 00:59:50 bar, since the difference is only 10 seconds (<1%), right?

[MysticSloth]

Since May you can solve this problem by using measure calculating only minutes in y-axis and original measure on custom data label. 

Take hours out of the original measure:

duration in minutes =

VAR Duration = SUM(Sheet1[Call Duration total seconds])

VAR Hours = INT ( Duration / 3600)

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

RETURN

(Hours * 60 + Minutes) * 100 + Seconds

And use the original measure in Visual->Data Labels->Values->Custom label. Maybe hide y-axis if you feel like it.

Like this

[BugmanJ]

It would be great if you could show how this is now being done with an example please? Thank you!!

[Greg_Deckler]

@strifler Well, the underlying numbers are 10000 and 6000 so, yes, that is an issue.

[Anonymous]

@Greg_Deckler  Hi Greg.  I cannot get this to work.  It shows the values as billions (or millions if I narrow the scope down).  Note: I only need it to show hours and minutes.

 

I have thoroughly examined Chelsie's file and can find no issue.  Can you help?

[Greg_Deckler]

@Anonymous So this happened in another thread dealing with this. I believe the fix was to go into the Values area of the visual and turn off auto display format and change it to None and then the custom format string worked as designed.

[Patrick_AW]

@Greg_Deckler I have a similar issue as mentioned by @Anonymous .

But in my case I have succeded in turning off auto display format and changing it to None. The custom format string 00:00:00 worked as designed.

I will like to display only hours and minutes eg. 12:34 and not 12:34:56.

How do I go about this? Using 00:00 displays 1234:57

[VizKid]

@Greg_Deckler I have similar requirements

In my case I have also setup proper formating and successfully shown on Graphs with Auto off with HH:MM and MM:SS - I am really glad for your post!! 🙂

In addition user wants to view it in DD:HH:MM, Any idea how can I get to show in this format?

Thanks so much

[Greg_Deckler]

@VizKid I am thinking this:

// We start with a duration in number of seconds
VAR Duration = SUM([Duration])
// There are 86,400 seconds in an day
VAR Days = INT ( Duration / 86400)
// There are 3600 seconds in a hour
VAR Hours = INT ( MOD( Duration - ( Hours * 86400 ),86400 ) / 3600)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Minutes = INT (MOD ( MOD( Duration - ( Hours * 86400 ),86400 ), 3600 ) / 60)
RETURN
// We put the hours, minutes and seconds into the proper "place"
Days * 10000 + Hours * 100 + Minutes

[VizKid]

Awesome - thanks so much @Greg_Deckler  for your valueable help/feedback 🙂

I think I got it (Made one minor change). wanted to loop you back in... When I try to use DAX above, there was a typo maybe...

VAR Hours = INT ( MOD( Duration - ( Hours * 86400 ),86400 ) / 3600)

So I updated to resolve - Thoughts?

// We start with a duration in number of seconds
VAR Duration = SUM([Duration])
// There are 86,400 seconds in an day
VAR Days = INT ( Duration / 86400)
// There are 3600 seconds in a hour
VAR Hours = INT ( MOD( Duration - ( Days * 86400 ),86400 ) / 3600)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Minutes = INT (MOD ( MOD( Duration - ( Days * 86400 ),86400 ), 3600 ) / 60)
RETURN
// We put the hours, minutes and seconds into the proper "place"
Days * 10000 + Hours * 100 + Minutes