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 have time values in format HH:MM:SS and I would like to be able to summarize values in that format. I found code below which is working, but the format is DD:HH:MM:SS, for example 34days02:59:27.
But I dont want to show the summary in days, I want to show total hours, minutes and seconds. Instead of 34days02:59:27 it should show 818:59:27
Anyone who can help me solve this?
newDuration =
VAR TotalSeconds=SUMX('Tiddata';HOUR('Tiddata'[CopyDuration])*3600+MINUTE('Tiddata'[CopyDuration])*60+SECOND('Tiddata'[CopyDuration]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds;3600)/60)
VAR Secs = MOD(TotalSeconds;60)
return IF(DAYS=0;"";IF(DAYS>1;DAYS&"days";Days&"day"))&IF(Hors<10;"0"&Hors;Hors)&":"&IF(Mins<10;"0"&Mins;Mins)&":"&IF(Secs<10;"0"&Secs;Secs)
Solved! Go to Solution.
Try this:
newDuration = VAR TotalSeconds = SUMX ( 'Tiddata'; HOUR ( 'Tiddata'[CopyDuration] ) * 3600 + MINUTE ( 'Tiddata'[CopyDuration] ) * 60 + SECOND ( 'Tiddata'[CopyDuration] ) ) VAR Hors = TRUNC ( TotalSeconds / 3600 ) VAR Mins = TRUNC ( MOD ( TotalSeconds; 3600 ) / 60 ) VAR Secs = MOD ( TotalSeconds; 60 ) RETURN IF ( Hors < 10; "0" & Hors; Hors ) & ":" & IF ( Mins < 10; "0" & Mins; Mins ) & ":" & IF ( Secs < 10; "0" & Secs; Secs )
I changed the Hors variable to
VAR Hors =
TRUNC ( TotalSeconds / 3600 )
and then removed any code having to do with the Days variable.
Hi again,
The problem I have now is that the new measure is not working with the date table I have. The total value for a specific date is correct, but in the table I can see all the all values for all dates and not only the one I selected in a filter. This is the case only for the created measu newDuration, for other measures in table Tiddata the date table is working fine. See issue in table below:
Try this:
newDuration = VAR TotalSeconds = SUMX ( 'Tiddata'; HOUR ( 'Tiddata'[CopyDuration] ) * 3600 + MINUTE ( 'Tiddata'[CopyDuration] ) * 60 + SECOND ( 'Tiddata'[CopyDuration] ) ) VAR Hors = TRUNC ( TotalSeconds / 3600 ) VAR Mins = TRUNC ( MOD ( TotalSeconds; 3600 ) / 60 ) VAR Secs = MOD ( TotalSeconds; 60 ) RETURN IF ( Hors < 10; "0" & Hors; Hors ) & ":" & IF ( Mins < 10; "0" & Mins; Mins ) & ":" & IF ( Secs < 10; "0" & Secs; Secs )
I changed the Hors variable to
VAR Hors =
TRUNC ( TotalSeconds / 3600 )
and then removed any code having to do with the Days variable.
Hi ChrisHaas,
It's working 🙂
This really made my day, I really needed this to start off the week right 😄
BR
Nazeem
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |