Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
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
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?
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 The issue I have now is that I get a ":" for values that should be blank. I tried an if statment but cant get it to work properly. any suggestions?
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
**bleep**, Your good! Thank you so much. I have been trying to figure this out for a month! WOrked like a charm!
Worked like a charm! Thanks so much!
Currently my formula is... Worked = SUMX(SUMMARIZE(VALUES(ReportA_PQ[Date]),[Date],"ABCD",[Logout]-[Login]-[Lunch]),[ABCD])
anyone have any ideas?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |