Sum of duration of task with Days, Hours, Minutes

[jagostinhoCT]

Hello,

While taking the first baby steps into Power BI, I was trying to get grips with how to sum time spent on a particular project and got stuck.

Say that I have a table where each row represents different time entries, each being assigned to different projects, as below.

Creating a measure to sum the duration would be expected to return a sum of 36h, which is not.

It is showing 12h instead. I understand that it may be deducting the first 24h from the lot. Is there a way that I can format the SumOfDuration column to keep adding up the hours and report values such as [Days][Hours][Minutes]?

Thank you for your help.

[asocorro · Accepted solution]

I was able to get it to work:

I did two things.  I created a measure SumOfTime defined as above.  And I set the format of the Time field and of this measure to "(H:mm)".

[Niflis]

Hi again. just noticed that once i started to filter, it actually changed.

sorry for that.

But now i'm having issues to get it to show average (can't understand what you explain above)

and also i want to add this to a column chart. and right now it only counts the measure and column as it is in text type.

but i can't change it to anything else as it will only result in error.

what am i missing?

thank you in advance.

[jmlipari]

Hi,
I'm working with time intervals in PowerBi. This topic was very useful for me but I need a little more help from you.

I have a table in the model (Orders) with #Order, StartTime, EndTime and ElapsedTime in seconds and I need to visualize the total elapsed time in format hhh:mm:ss (Elapsed Time is usually greather than 24 hours)

I did two measures to see the problem:
The first (Without Text) realize the calculations to express elapsed time in the new format, but I've trimmed the text formatting for testing purposes.

Without Text = VAR Duration = SUMX('Reportes de Operaciones';'Reportes de Operaciones'[ElapsedTime (s)])
VAR Hours = INT(Duration/3600)
VAR Minutes = INT(MOD(Duration;3600)/60)
VAR Seconds = MOD(Duration;60)
RETURN
CONCATENATE (Hours; CONCATENATE (Minutes;Seconds ))

The Second (Real Time), do the same calculations, but I've added format to display elapsed time in hhh:mm:ss

Tiempo Real = VAR Duration = SUMX('Reportes de Operaciones';'Reportes de Operaciones'[ElapsedTime (s)])
VAR Hours = INT(Duration/3600)
VAR Minutes = INT(MOD(Duration;3600)/60)
VAR Seconds = MOD(Duration;60)

VAR H =
IF ( LEN ( Hours ) = 1;
CONCATENATE ( "0"; Hours );
CONCATENATE ( ""; Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1;
CONCATENATE ( "0"; Minutes );
CONCATENATE ( ""; Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1;
CONCATENATE ( "0"; Seconds );
CONCATENATE ( ""; Seconds )
)

VAR tH = IF(Hours=0;"";IF(Hours>1;Hours&"h: ";Hours&"h: "))
VAR tM = IF(Minutes=0;"";IF(Minutes>1;Minutes&"m: ";Minutes&"m: "))
VAR tS = IF(Seconds=0;"";IF(Seconds>1;Seconds&"s";Seconds&"s"))
RETURN
CONCATENATE (th; CONCATENATE ("";CONCATENATE (tm;CONCATENATE (""; CONCATENATE (ts;"" )) )))

Now..
In Table 1 you see that "Without text" measure works perectly but show unformatted data (obviously).
In Table 2 you see that "Tiempo Real" measure shows formatted data but.... I've lost de filter context.

How do I to fix this? My brain burns...

[Acyrus1992]

Hi mate

Is this still a problem for you?

[Anonymous]

Hello Acyrus1992,

Thank you very much again!!! for the valuable information.

Yes, I'm using it for ITSM database and incident table, however i'm using your code to utilizing for calculating MTTR in hours and but actually not for technician logged hours.

I'm trying calculate the incident duration i.e MTTR-W/E - (Mean Time To Resolve With Excludes) ticket duration which excludes time period awaiting for 3rd party and awaiting for user for particular ticket.

When I download the incident data from ITSM DB into excel in seconds format and I have calculated MTTR very easily in excel as it directly gives in seconds format(Screenshot below).

However, when I'm using ITSM DB - import method via Azure SQL DB to build PowerBI dashboard to reduce manual efforts, it has given in months, days, hours, & mins format which is impossible to calculate MTTR of tickets.

Please find screenshot of excel sheet calculated column and pivot table.

Pivot Table

Cheer's,

Nawal

+91-9866755720

[Acyrus1992]

Hi @Anonymous

OK - lets start from the top.

In PowerBI Desktop - do you have a column with jsut the seconds in it? (imported from DB?)
Or - Do you have the "Start Date" and "End Date" from the DB? (From which you can get seconds (End Date - Start Date)?

If you do - we can get the total seconds figure by basically doing a simple calculation like this: [CONVERT TO SECONDS(End Date - Start Date)] - [(3rd party time/unSupported Time in seconds)]

What data do you have available to use in the Data View?

[Anonymous]

Hello Acyrus1992,

In PowerBI Desktop - do you have a column with just the seconds in it? (imported from DB?)

 > Nawal - Nope, the data isn't  in seconds format when I imported from DB. Its in Days hours minutes format

Or - Do you have the "Start Date" and "End Date" from the DB? (From which you can get seconds (End Date - Start Date)?

 > Nawal - It does Not have start date and end date. Its just duration of ticket.

What data do you have available to use in the Data View?

> Nawal - data is in below format

 

Cheers

Nawal

[Acyrus1992]

Hi @Anonymous

Where are you getting the Total Elapsed Time from?

Is the only (date/time/duration) related data you have available in PowerBI Desktop - Data View - this :

> Nawal - data is in below format

 

[Anonymous]

Hello Acyrus1992

Where are you getting the Total Elapsed Time from?

> Nawal - When I download it from reporting to excel, its automatically converting in to seconds format and I use formula to convert from seconds to hours by (column name / 3600).

I'm really not sure, how its converting.

Is the only (date/time/duration) related data you have available in PowerBI Desktop - Data View - this :

> Nawal - yes its only 1 column with duration, however its in text format.

If you are OK, I can share you my screen.

My skype ID: snawalkishore@hotmail.com.

Cheers,

Nawal

[Acyrus1992]

Hi @Anonymous

I have build a solution for you (I think)
You will need to create 4 new Columns in your Dataview.

Do this:

Column1:

TIME_Days = (IF(FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number")="","0",FORMAT(LEFT(TestData[Actual_Elp_Duration],FIND(" Days",TestData[Actual_Elp_Duration],1,0)),"General Number"))) * 1440

Column2:

TIME_Hours = SUBSTITUTE(IF(SEARCH("Hours",TestData[Actual_Elp_Duration],1,"0")=0,"0",IF(SEARCH("Days",TestData[Actual_Elp_Duration],1,0)<>0,RIGHT(LEFT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Days",""),5),3),LEFT(TestData[Actual_Elp_Duration],2)))," ","")*60

Column3:

TIME_Minutes = (IF(SEARCH("Minutes",TestData[Actual_Elp_Duration],1,0)=0,"0",RIGHT(SUBSTITUTE(TestData[Actual_Elp_Duration]," Minutes",""),2))*1)

Column4:

TIME_Total_Minutes = TestData[TIME_Days]+TestData[TIME_Hours]+TestData[TIME_Minutes]

This should give you you total times in a int format of minutes 🙂

[NavyaMounika]

Also,I have tried to use the above formula... However, I'm getting an error.. for the same I have attached snapshot.

[NavyaMounika]

Hi, 

Also,I have tried to use the above formula... However, I'm getting an error.. for the same I have attached snapshot.

I am also facing similar kind of issue with more possibilites. 

Please help me with the possible resolution on the below mentioned elapsed time.

Here i have to convert the below elapsed time in to seconds.

Business Elapsed Time

10 Minutes

0 Seconds

2 Hours 30 Minutes

1 Hour 32 Minutes

4 Hours 1 Minute

2 Hours

1 Hour

1 Hour 1 Minute

1 Hour 16 Minutes

1 Minute

1 Hour 46 Minutes

5 Days 12 Hours 16 Minutes

1 Day 10 Hours 23 Minutes

1 Day 1 Hour 46 Minutes

5 Days

1 Day 13 Minutes

1 Day 8 Hours

295 Days 22 Hours 2 Minutes

1 Day 27 Minutes

1 Day 1 Hour 15 Minutes

1 Day 4 Hours 1 Minute

6 Days 42 Minutes

1 Day 17 Hours 42 Minutes

1 Day 5 Minutes

6 Days 10 Hours 59 Minutes

10 Days 21 Hours 40 Minutes

1 Day 2 Hours 18 Minutes

2 Days 12 Hours 28 Minutes

1 Day 2 Hours 12 Minutes

2 Days 21 Hours 9 Minutes

22 Days 12 Hours

1 Day 37 Minutes

2 Days 11 Hours 5 Minutes

15 Days 1 Hour

2 Days 7 Hours 52 Minutes

2 Days 9 Hours 40 Minutes

9 Days 15 Hours 50 Minutes

7 Days 21 Hours

2 Days 27 Minutes

162 Days 15 Hours 51 Minutes

1 Day 9 Hours 19 Minutes

1 Day 5 Hours 47 Minutes

26 Days 3 Hours 10 Minutes

2 Days 9 Hours 44 Minutes

1 Day 12 Hours 23 Minutes

5 Days 23 Minutes

155 Days 1 Hour 46 Minutes

1 Day 3 Hours 14 Minutes

5 Days 14 Hours 40 Minutes

2 Days 1 Hour 5 Minutes

2 Days 1 Hour 47 Minutes

6 Days 22 Hours 6 Minutes

13 Days 3 Hours 15 Minutes

12 Days 9 Hours

1 Day 15 Hours 36 Minutes

1 Day 11 Hours 46 Minutes

1 Day 6 Hours 41 Minutes

9 Days 18 Hours 24 Minutes

8 Days 20 Hours 7 Minutes

1 Day 16 Hours 9 Minutes

3 Days 17 Hours 8 Minutes

1 Day 14 Hours 32 Minutes

6 Days 18 Hours

4 Days 6 Hours 9 Minutes

2 Days 6 Hours 48 Minutes

27 Days 22 Hours 30 Minutes

5 Days 19 Hours 2 Minutes

1 Day 3 Hours 34 Minutes

4 Days 7 Hours 11 Minutes

1 Day 6 Hours 39 Minutes

2 Days 5 Hours 34 Minutes

1 Day 6 Hours 26 Minutes

1 Day 8 Hours 8 Minutes

9 Days 18 Hours 23 Minutes

1 Day 6 Hours 1 Minute

1 Day 1 Hour 10 Minutes

1 Day 1 Hour 1 Minute

2 Days 2 Hours

1 Day 1 Hour

6 Days 43 Minutes

1 Day 1 Hour 37 Minutes

1 Day 3 Hours

1 Day 8 Hours 28 Minutes

2 Days 17 Hours 1 Minute

1 Day 59 Minutes

[foodd]

Hello @NavyaMounika,  thank you for your contribution to the Fabric Community Forum. Since the original post has been marked as "Solved" and is over eight years old, we kindly ask that you create a new post for your inquiries instead of replying to the existing one. Our community is here to assist you, and following this approach will help ensure that your question is viewed by more people and answered more promptly.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[Anonymous]

Hello Acyrus1992,

I do not have words...

I'm really thankful to you. Its working.

I will be in touch with you. I'm Beginner of PowerBI. I'm sure I would definitely request your expertness when I run into Logical or arithmetical issues.

Thank you,

Nawal

+91-9866755720

[jagostinhoCT]

@Eric_Zhang

This seems to work. Thank you very much for your time.

So, in order for me to learn "how to fish" rather than just using the fish given (apologies for some misuse of idioms)...

What you did was creating a new measure "newDuration".

You then set out to create different variables VAR to make it easier to use on the IF THEN expression below.

First VAR converts the Date/Time value into seconds only, which are then used to convert the other VARs (days, hours, mins, secs)

You truncated (TRUNC) each VAR in order to display integer values for each of the time units.

Is this it? And because the newDuration is a Date/Time field I can used it to drill down data and use it for any other date/time analysis for my data?

[Eric_Zhang]

@jagostinhoCT

Is this it? And because the newDuration is a Date/Time field I can used it to drill down data and use it for any other date/time analysis for my data?

Yes. And newDuration is not a Date/Time, it is a text. Because"x days 00:00:00" is neither a valid date nor a time format, so I think you can't use it as Date/Time and drill down.

[jagostinhoCT]

@Eric_Zhang

So, I may need to create different calculates columns, then, as I may need to report things differently. Or I may still misintepreting what are my options here. I explain.

One with values formated as Date/Time, or just Time, where I get a whole number for the time unit I care (minutes or hours, say). For this column I would need to first convert all Date/Time data into that time unit first. But after that I can create different measures such as Sums, Averages, whatever. And these can in turn be used on the production of visuals.

Another column with values formated as Text, where I use the "newDuration" to display the data broken down into days, hours, mins. This column would be used whenever tables and matrixes are involved.

All this because it is not currently possible to get a Date/Time field where one can extract duration values directly.

Does this make sense?

Joaquim

[asocorro · Accepted solution]

I was able to get it to work:

I did two things.  I created a measure SumOfTime defined as above.  And I set the format of the Time field and of this measure to "(H:mm)".

[jagostinhoCT]

Thank you for the feedback.

That one works because the sum is less than 24h.

Try adding more rows so that the SumOfTime is larger than 24h. Does it report that figure?

[asocorro]

Sorry, I misinterpreted your statement.  What happens is that the Time data type is for representing the hours in a day, so it will not "accumulate" as you wish it would.  It is not meant for representing an arbitrary number of hours.  I think you will have to convert it to a decimal number, as in 8:30 being 8.5.  Or include dates, if it's possible.