Unable to Correctly Calculate the Sum of Seconds in hh:mm:ss (Power BI)

[srmedic88]

Good Morning,

Any help with using the correct calculation method(s) to provide accurate data would be appreciated! The goal is to provide the time displayed in HH:MM:SS

I am having difficulty in calculating the total seconds from the Datediff Calculation in Seconds:

(TOT Sum Seconds = DATEDIFF( 'View_EMS_Reports_Basic_Incident'[Dispatched Time], 'View_EMS_Reports_Basic_Incident'[Available Time],SECOND)

I can display the total seconds in various formats, however when I attempt to calculate the total by any format it is not calculating correctly. I have tried many posted solutions without success. 

I am not sure if there is an underlying issue with calculating the rows as some have duplicate rows of data, the Incident Number is unique for the dataset...

Screenshot below is a sample exported to Excel. When I total the "Time on Task Min" column in Excel it appears to be correct. (that data is rounded up to nearest minute)

sorry I cannot attach the excel file...

I am new to PBI and the forum, apparently I cannot attach files due to my current permission level 

The measure I created in PBI "TOT Test Full Duration" is correct in some of the rows, but there are many rows where it is way off for some reason? I highlighted those in the sample data attached.

This is the calculation for that measure:

TOT Test Full Duration =
VAR _Seconds = [TOT Sum 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 ( [TOT Sum Seconds] ),
FORMAT ( _Hours, "00" ) & ":" &
FORMAT ( _RemainingMinutes, "00" ) & ":" &
FORMAT ( _RemainingSeconds, "00" )
)

[Omid_Motamedise]

USe this one

TOT Test Full Duration =
VAR _Seconds = [TOT Sum 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 ( [TOT Sum Seconds] ) && _Seconds >= 0,
FORMAT ( _Hours, "00" ) & ":" &
FORMAT ( _RemainingMinutes, "00" ) & ":" &
FORMAT ( _RemainingSeconds, "00" ),
BLANK() -- Handles rows where seconds calculation is invalid
)

[srmedic88]

Hello to all who have assisted me so far.

I think I am on DAX overload, to date I have spent hours attempting to create a calculation that shows the sum of Time on Task using a variety of measures, calculated columns, etc... that I have found in the forum. I am missing something somewhere with this measure...

It appears the other measures or calculated I have created are working correctly (I think) for Average, Median, 90th percentile, etc... Maybe someone can review the work and provide some guidance if something doesn't look right. I am pretty new to PBI and am trying to learn the ins and outs of DAX.

I removed all of the sensitive data in the pbix file in the link below. Hopefully this will shed some light on the difficulties experienced.

https://dni.box.com/s/i8yar9i101o3lac1sjrx5xb3sgma19vq

Happy New Year as well!!

[lbendlin · Accepted solution]

Here's your basic column:

Time on Task = [Available Time]-[Dispatched Time]

If you want the minutes, multiply by 1440.

Time on Task = ([Available Time]-[Dispatched Time])*1440

etc.

Note:  There are rows where [Available Time] is blank - how should these be handled?

[v-aatheeque]

Hi @srmedic88 
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @srmedic88 ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @srmedic88 ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[shafiz_p]

Hi @srmedic88  In your measure, you have referenced naked calculated column "TOT Sum Seconds", your formula not telling that. Check out:

TOT Sum Seconds = DATEDIFF( 'View_EMS_Reports_Basic_Incident'[Dispatched Time], 'View_EMS_Reports_Basic_Incident'[Available Time],SECOND)

If this is a measure, you just can't referenced naked column in datediff. You need to use aggregator function like max or min.

You have created a measure, named "TOT Test Full Duration" but referenced column [TOT Sum Seconds], without aggregation. Also, this would not be possible : NOT ISBLANK ( [TOT Sum Seconds] ).

Why you not received error instead of wrong calculation, I don't know. 

Any way, want you to create a calculated column first as "TOT Sum Seconds". See image:

Then, you could try the below , try change according to your need:

Total Duration Measure = 
VAR _TotalSeconds = SUM('View_EMS_Reports_Basic_Incident'[TOT Sum Seconds])
VAR _Minutes = INT(DIVIDE(_TotalSeconds, 60))
VAR _RemainingSeconds = MOD(_TotalSeconds, 60)
VAR _Hours = INT(DIVIDE(_Minutes, 60))
VAR _RemainingMinutes = MOD(_Minutes, 60)
RETURN
FORMAT(_Hours, "00") & ":" &
FORMAT(_RemainingMinutes, "00") & ":" &
FORMAT(_RemainingSeconds, "00")

I have found output below:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos.

Best Regards,
Shahariar Hafiz

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[srmedic88]

Here is a link to download the sample data exported to Excel. It should have the data required, if not please advise. I cannot send the current pbix file due to privacy related content. Thanks in advance.

https://dni.box.com/s/wunzh07oqzao4h8fhpty0a0arp8g9liu

[lbendlin]

I can't see how your sample data relates to the issue.

Keep in mind that you can do standard math with dates and times. Multiply your datetime value by 84600 to get the number of seconds.  you can then subtract two timestamps to get the overall number of seconds. Divide the result by 84600, keep the integer part as the number of days and show the fractional part in time format.