skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Partners Overview
    • Solutions Partners
    • BI Specialized Partners
    • Power BI CSOs
    • Fabric Partner Community
    • Training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Power BI forums
    • Updates
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish forums
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 

    Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

    • Power BI forums
    • Galleries
    • Quick Measures Gallery
    • Re: Chelsie Eiden's Duration

    Re: Chelsie Eiden's Duration

    10-19-2021 11:32 AM

    Patrick_AW
    New Member
    25098 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Chelsie Eiden's Duration

    ‎09-16-2019 01:12 PM

    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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    72 KB
    ChelsieEidensDuration.pbix
    Labels:
    • Labels:
    • Other
    • Time Intelligence
    • Totals
    Message 1 of 20
    33,801 Views
    10
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    bgriff135
    bgriff135
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-07-2022 09:56 AM

    @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!

    bgriff135_0-1649350483429.png

     

    ChelsieEidensDuration with Legend.pbix
    Message 19 of 20
    19,362 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to bgriff135
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-08-2022 08:48 AM

    @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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 20 of 20
    19,218 Views
    0
    Reply
    strifler
    strifler
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-24-2022 04:15 AM

    Hi Greg!

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

    strifler_0-1645704763914.png

    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?

    Message 15 of 20
    20,872 Views
    0
    Reply
    MysticSloth
    MysticSloth
    New Member
    In response to strifler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-25-2023 11:49 PM

    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 thisLike this
     
     
    Message 17 of 20
    5,464 Views
    0
    Reply
    BugmanJ
    BugmanJ Advocate II
    Advocate II
    In response to MysticSloth
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-26-2023 05:20 AM

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

    Message 18 of 20
    5,514 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to strifler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-24-2022 05:16 AM

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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 16 of 20
    20,863 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-27-2021 02:43 PM

    @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.

     

    Ruthie09_0-1630099929253.png

     

     
     

    Images.jpg

     

    Ruthie09_3-1630100501460.png

     

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

     

    Message 4 of 20
    26,467 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-28-2021 08:37 AM

    @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.


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 5 of 20
    26,437 Views
    1
    Reply
    Patrick_AW
    Patrick_AW
    New Member
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-19-2021 09:30 AM

    @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

    Preview file
    36 KB
    Message 6 of 20
    25,123 Views
    0
    Reply
    VizKid
    VizKid
    Frequent Visitor
    In response to Patrick_AW
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-19-2021 05:44 PM

    @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

    Message 9 of 20
    25,066 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to VizKid
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-20-2021 05:28 AM

    @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

    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 10 of 20
    25,008 Views
    1
    Reply
    VizKid
    VizKid
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-20-2021 08:58 AM

    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



    Message 11 of 20
    24,969 Views
    1
    Reply
    Torok
    Torok
    Frequent Visitor
    In response to VizKid
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-25-2023 02:55 PM

    Thank you so much for the addition of days to the formula. This is exactly what I was looking for and it worked.

     

    Message 14 of 20
    4,462 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to VizKid
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-21-2021 04:48 AM

    @VizKid Yep, you got it, good catch.


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 12 of 20
    24,866 Views
    2
    Reply
    VizKid
    VizKid
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-25-2021 07:16 AM

    Awesome - Thanks so much @Greg_Deckler . You are amazing, really appreciate your help!! 🙂

    Message 13 of 20
    24,679 Views
    2
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Patrick_AW
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-19-2021 10:41 AM

    @Patrick_AW Try changing the last line to:

     

    Hours * 100 + Minutes

     Then try a custom format string like 0000:00 or 00:00


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 7 of 20
    25,107 Views
    1
    Reply
    Patrick_AW
    Patrick_AW
    New Member
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-19-2021 11:32 AM

    Thanks Greg.

     

    Custom format of 00:00 works with the above.

    Message 8 of 20
    25,098 Views
    2
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-12-2020 12:43 AM

    Thanks Greg.

    It's working as expected and it resolved the issues in my report.

     

    Thank you so much for your help.

     

    Regards,

    Umasankar

     

    Message 2 of 20
    32,830 Views
    0
    Reply
    DLROLLINGS
    DLROLLINGS Helper I
    Helper I
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-16-2020 08:11 AM

    Hi all

     

    Not going to suggest I 100% understand this, been working blind on Power Bi and came across this whilst lookingn to sum time to be more than 24:00:00. 

     

    This issue I have with this is that I want to use this to calculate the number of working hours based on a number of days, i.e 07:00:00 * 7 should be 49:00:00. 

     

    Due to the data, I have had to create a column called Working Hours, this figure is repeated against each action so I am having to use the average of that column against the number of days....with me so far? Not sure I am. 

     

    Anywho, when changing the Sum([Duration]) to Average([Working Hours])*{Days]

     

    The rest remains the same however I get results such as 08:45:00 * 5 = 7.00 rather than 43:45:00

     

    Anyone able to point me in the right direction here? Much appreciated.

    Message 3 of 20
    32,574 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices