cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amerjaroudi
New Member

Duration Time more than 24 hours

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 ?

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

mahoneypat
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
 
Capture.PNG

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.