Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jagostinhoCT
Post Partisan
Post Partisan

Sum of duration of task with Days, Hours, Minutes

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.

SumOfDuration.png

SumOfDuration.png

SumOfDuration.png

2 ACCEPTED SOLUTIONS
asocorro
Skilled Sharer
Skilled Sharer

I was able to get it to work:

 

t.png

 

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)".

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

Eric_Zhang
Microsoft Employee
Microsoft Employee

@jagostinhoCT

To get the expected output, you can follow below steps.

  1. copy the duration column to a new column, say named copyDuration
  2. change the copiedDuration to date/time type
  3. create a new measure as below
    newDuration = 
    VAR TotalSeconds=SUMX('Sheet2 (2)',HOUR('Sheet2 (2)'[CopyDuration])*3600+MINUTE('Sheet2 (2)'[CopyDuration])*60+SECOND('Sheet2 (2)'[CopyDuration]))
    VAR Days =TRUNC(TotalSeconds/3600/24)
    VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
    VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
    VAR Secs = MOD(TotalSeconds,60)
    return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

Capture.PNG

View solution in original post

40 REPLIES 40

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.

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.
PowerBiPowerBi
How do I to fix this? My brain burns...

 

 

 

 

 

 

 

 

Hi mate

 

 

Is this still a problem for you?

 

 

 
Anonymous
Not applicable

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.

Excel_duration_cal_column.PNG

Pivot Table

Excel_duration_Pivot.PNG

 

Cheer's,

Nawal

+91-9866755720

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
Not applicable

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

 Data_veiw_Date_in_powerbi.PNG

 

Cheers

Nawal

Hi @Anonymous

 

Where are you getting the Total Elapsed Time from?

 

example.PNG

 

 

 

 

 

 

 

 

 

 

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


> Nawal - data is in below format

 Data_veiw_Date_in_powerbi.PNG

 

 

Anonymous
Not applicable

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

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 🙂

Fix.PNG

 

 

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

 

NavyaMounika_0-1720029174486.png

 

Hi, 

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

 

NavyaMounika_0-1720029174486.png

 

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

 

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
Not applicable

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

 

 

@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?

 

@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.

 

@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
Skilled Sharer
Skilled Sharer

I was able to get it to work:

 

t.png

 

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)".

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

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?

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.

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.