Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nazeem81
Helper I
Helper I

Sum of duration

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)

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

3 REPLIES 3
Nazeem81
Helper I
Helper I

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:

 

Skärmklipp.PNG

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.