Duration Time more than 24 hours

[amerjaroudi]

Hi,

once we group one cell and having multiable duration and SUM of them is equal more than 24 hours as duration is not appeared all total example 20:10:10 (HH:MM:SS) + 10:10:05 it should shows as 30:20:15 ..

Any help here ?

[PhilipTreacy · Accepted solution]

Hi @amerjaroudi 

You need to treat travel time as a duration in Power Query then bring that into PBI Desktop where it will be a decimal.

You can then write a measure to display your travel time in a friendly H:M:S format - see this solution for code and an explanation

Solved: Converting decimal hours to friendly format (D:H:M... - Microsoft Power BI Community

Leaving the travel time as decimal in PBI means you can do maths with it.

You can uplaod your PBIX file to OneDrive or Dropbox and then link to it from here.

Regards

Phil

[mahoneypat]

If your column is already a duration, you can add a custom column with

= Duration.TotalHours([DurationColumn]

If your column is a Time, you can add a custom column with

= Duration.TotalHours([Time]-#time(0,0,0))

Either way, you'll end up with hours as a decimal that you can then add up for analysis.  You could use the other Duration.Totalxxx functions if you don't want hours.

Pat

[PhilipTreacy]

Hi @amerjaroudi 

Please always supply sample data so we don't have to guess what you are doing.

I presume you are talking about Durations in Power Query?  But by the format you are using you may be using Times rather than actual Durations?

A Duration in Power Query is actually a decimal number describing whole days e.g. 1.25 is 1 day and 8 hours.

When you bring these durations into Power BI (the Data Model) you'll see them as decimals.

So to add durations you first must store them as durations, and adding them is just as simple as adding decimals.

Please provide some sample data so I can see what you are actually doing.

Regards

Phil

[amerjaroudi]

Thanks for your reply ..

I made time differance between two columns "Depart" and "Arrival" and called "Travel_Time" as duration in (Query Editor) then I choose "Region" coulmn to table visualization and didn`t find (sum of travel_time) only count found it,, I make new measure of Travel time "

SumTravel = SUM('CM History'[Travel_Time])" and added to same table, first row (region_1) the sum should be 1923:47:21, this is my concene here it not showing if more than 24 hours 

also let me know how I can upload the sample data source