cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Junaid11
Helper III
Helper III

Sum of Time

I have two columns one with arrival time and and other with departure. I took the difference and then created another measure to get the sum of difference. It looks like below.

sddssdss.PNG

It is working fine if only one day is choosen but when ever I choose multiple dates like arrival and departure time differnce and their sum it does not show correct sum. What can I do to get correct sum no matter how many days sum is being done.

The picture with wrong sum is shown below.

asddsass.PNG

Kindly let me know if there is a way to get the correct sum. It wold be very helpful.

Thanks

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Junaid11 

The cleanest way I have found to deal with formatted time is to..

  1. Calculate the duration as seconds in a column
  2. Add a measure that sums the duration in seconds
  3. Use the duration measure in a formatting measure like this.

 

Call Duration = 
VAR _Seconds = [Call Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
    IF (
        NOT ISBLANK ( _Seconds ),
            FORMAT ( _Hours, "00" ) & ":" & 
            FORMAT ( _RemainingMinutes, "00" ) & ":" & 
            FORMAT ( _RemainingSeconds, "00" )
    )

 

 

View solution in original post

1 REPLY 1
jdbuchanan71
Super User
Super User

@Junaid11 

The cleanest way I have found to deal with formatted time is to..

  1. Calculate the duration as seconds in a column
  2. Add a measure that sums the duration in seconds
  3. Use the duration measure in a formatting measure like this.

 

Call Duration = 
VAR _Seconds = [Call Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
    IF (
        NOT ISBLANK ( _Seconds ),
            FORMAT ( _Hours, "00" ) & ":" & 
            FORMAT ( _RemainingMinutes, "00" ) & ":" & 
            FORMAT ( _RemainingSeconds, "00" )
    )

 

 

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!