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.