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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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

37 REPLIES 37

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

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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