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...
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?
     
     
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?
       
       
      • mariner84's avatar
        mariner84
        Advocate I
        6 years ago

        Anonymous 

         

        This is great, I'll be able to work with this. Appreciate you helping me out!