Forum Discussion

mariner84's avatar
mariner84
Advocate I
6 years ago
Solved

Time and Duration Help

Hello,

I have a data set where I have duration in seconds, which I'm looking to convert to a format to show days, hours, minutes and seconds. Once converted, I'm then hoping it's possible to put the information on a graph where I can show average duration on the y-axis and the month on the x-axis based on my other column called Closed Date.

 

I could not figure out how to use query editor to convert the duration from seconds to the format I am requiring. I've also never seen graphs in Power BI where the y-axis is time.

 

Below is a sample excel of my data and a pbix

Duration - Excel 

PBIX 

Any help would be really appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    mariner84 

     

    What if you generate these columns:

     

    DAYS = TRUNC([Business Duration]/86400)

    HRS = TRUNC(MOD([Business duration]/86400,1)*24)
    MIN = TRUNC(MOD(MOD([Business duration]/86400,1)*24,1)*60)
    SEC = TRUNC(MOD(MOD(MOD([Business duration]/86400,1)*24,1)*60,1)*60)
    FORMATTED = 'Page 1'[DAYS]&":"&'Page 1'[HRS]&":"&'Page 1'[MIN]&":"&'Page 1'[SEC]
     
    Does that help?
     
     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    mariner84 

    I had this issue and I found out that Power BI doesn't support showing Y Axis duration data as time in that DD:HH:MM:SS format. I had a data set that calculates an average duration in minutes, and I made a line graph to show that average duration vs day. I created measures:

     

    TruckWaitMin = (AVERAGEX(TAG,CALCULATE(SUM ([ScaleOut]) - SUM([Arrive])) * 1440))
    HRS = TRUNC([TruckWaitMin]/60)
    MIN = TRUNC([TruckWaitMin]-[HRS]*60)
    WAITDuration = RIGHT("0"&[HRS],2)&":"&RIGHT("0"&[MIN],2)  <- this will give a formatted HH:MM duration
     
    The WAITDuration is added to the Tooltips. So the chart shows he duration in minutes, the toolips sows the formatted time.

     

    Line chart attached showing tooltip.

     

    • mariner84's avatar
      mariner84
      Advocate I
      6 years ago

      Anonymous 

       

      Thank you for responding and providing some insight into your data. It's too bad it cannot graph the data as required.

       

      I'm in need to graph the average duration (once converted to dd:hh:mm:ss) monthly to show the trend that is occurring. 

       

      Have you had to do similar reporting over that time instead of looking at it vs day?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        mariner84 

         

        What if you generate these columns:

         

        DAYS = TRUNC([Business Duration]/86400)

        HRS = TRUNC(MOD([Business duration]/86400,1)*24)
        MIN = TRUNC(MOD(MOD([Business duration]/86400,1)*24,1)*60)
        SEC = TRUNC(MOD(MOD(MOD([Business duration]/86400,1)*24,1)*60,1)*60)
        FORMATTED = 'Page 1'[DAYS]&":"&'Page 1'[HRS]&":"&'Page 1'[MIN]&":"&'Page 1'[SEC]
         
        Does that help?