Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Time/Duration Value Display

I am looking for a way to display total duration that is over 24 hours. 

 

Currently if the sum of two durations is 34:22 hours it will display as: 

 

10:22 

 

This must be because the Power BI value is based on a 24 hour clock. Therefore it is going to 24:00 then rolling back around to the 10:22 value. 

 

In excel you can fix this by using a custom time field as [hh]:MM. I dont see that option in Power BI anyone have any suggestions on how to fix this? 

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    I would try this, the format is text

    Measure = 
VAR Hours = INT([Total Duration]*24)
VAR Minutes = RIGHT(FORMAT([Total Duration],"hh:mm"),2)
RETURN
Hours & ":" & Minutes
  • Stachu's avatar
    Stachu
    8 years ago

    try this

    Measure = 
VAR Hours = INT([Total Duration]*24)
VAR Minutes = RIGHT(FORMAT([Total Duration],"hh:mm"),2)
VAR Separator = IF([Total Duration]=BLANK(),BLANK(),":")
RETURN
Hours & Separator & Minutes

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Currently my formula is... Worked = SUMX(SUMMARIZE(VALUES(ReportA_PQ[Date]),[Date],"ABCD",[Logout]-[Login]-[Lunch]),[ABCD])

     

    anyone have any ideas? 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    IF you want the result to be formatted as "HH:MM", its data type will be forced to text rather than date or numeric. Suppose you have generated a measure to get the total duration, to display total duration based on a 24 hour clock, please try this measure:

    Measure =
IF (
    VALUE ( LEFT ( [Total duration], 2 ) ) < 24,
    [Total duration],
    VALUE ( LEFT ( [Total duration], 2 ) )
        - INT ( VALUE ( LEFT ( [Total duration], 2 ) ) / 24 )
            * 24
        & ":"
        & RIGHT ( [Total duration], 2 )
)

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you so much for the guide. When I applied this measure it displayed "12/31/1899" for all of my measures. I tried changing the data type to time, text, general, whole number and none of them seemed to solve the issue. Any ideas? 

      • Stachu's avatar
        Stachu
        Community Champion
        8 years ago

        I would try this, the format is text

        Measure = 
VAR Hours = INT([Total Duration]*24)
VAR Minutes = RIGHT(FORMAT([Total Duration],"hh:mm"),2)
RETURN
Hours & ":" & Minutes