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

I'm having a similar challenge.  I have ticket durations calculated in minutes, using start and end times.  From this duration I've created measures to calculate the average, max and min durations for a period.  However the measures are reflecting minutes in whole numbers.  How can I convert the measures into a more user friendly format for reporting like "x Days, y Hours, z Mins"?

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

Hi @jagostinhoCT


Your solution here is superb - thank you!

I was hoping you could help me add months into this?
I have a sum calculation of time logged against tasks, and I'd like to include months spent too

I have this:

_________________________________________________________________________________

Tech Time Used 2 =
VAR TotalSeconds=SUMX(OC_Data,HOUR(OC_Data[Duration])*3600+MINUTE(OC_Data[Duration])*60+SECOND(OC_Data[Duration]))

 

VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)

 

return IF(DAYS=0,"",IF(DAYS>1,DAYS&" Days, ",Days&" Day, "))&IF(hors=0,"",IF(hors>1,hors&" Hours, ",hors&" Hour, "))&IF(Mins<10,"0"&Mins,Mins)&" Minutes"

 

This gives an output like so:

tim1.PNG

_________________________________________________________________________________

 

Ideally I'd like to show this:


time2.PNG

 

How could I achieve this?

 

Any help would be grand

 

Thank you

 

Anonymous
Not applicable

Hello Acyrus1992,


Thank you very much for solution.

 

And I have issue here, I request your expertise.

 

I have table name incidents which has column Actual Elapsed Duration and I wanted to convert in to seconds (column ACTUAL_ELP_DUR) with help of your DAX.

Actual Elapsed Duration is in Text format

As you suggested  i have updated the DAX as below 

ACTUAL_ELP_DUR =
VAR
TotalSeconds= SUMX(incident,HOUR(incident[Actual Elapsed Duration])*3600+MINUTE(incident[Actual Elapsed Duration])*60+SECOND(incident[Actual Elapsed Duration]))

VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)

return
IF(DAYS=0,"",IF(DAYS>1,DAYS&" Days, ",Days&" Day, "))&
IF(hors=0,"",IF(hors>1,hors&" Hours, ",hors&" Hour, "))&
IF(Mins<1,"0"&Mins,Mins)&" Minutes"

 

However its popping "Cannot convert value '31 Minutes' of type Text to type Date."

 

DAX_Date_errorDAX_Date_error 

 

Please suggest

 

Hello @Anonymous

 

First you need to have a column with the minutes without text: MinutesCapture.PNG

 

 

Im sure you can get to this, using whatever field you used to write the Elapsed Duration.

I'm guessing you are building the reports off the back of a itsm database.

 

 

 

 

 

 

 

Once you have a field with the pure minute figure do this:

 

 

 

Duration Format =
// Duration formatting
// We start with a duration in number of minutes
VAR Duration =SUMX(OC_Data,OC_Data[Minutes Logged by Technician])

VAR Mnths =  INT(Duration/43800)
VAR Days =    INT( (MOD(Duration,43200)/1440) )
VAR Hours =  INT(MOD(Duration,1440)/60)
// There are 60 seconds in a minute
VAR Minutes = MOD(MOD(Duration,1440),60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
//VAR Seconds =
// ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR MM =
IF (
LEN ( Mnths ) = 1,
CONCATENATE ( "0", Mnths ),
CONCATENATE ( "", Mnths )
)

VAR D =
IF (
LEN ( Days ) = 1,
CONCATENATE ( "0", Days ),
CONCATENATE ( "", Days )
)
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 )
)


VAR tMM = IF(Mnths=0,"",IF(Mnths>1,Mnths&" Months, ",Mnths&" Month, "))
VAR tD = IF(Days=0,"",IF(Days>1,Days&" Days, ",Days&" Day, "))
VAR tH = IF(Hours=0,"",IF(Hours>1,Hours&" Hours, ",Hours&" Hour, "))

RETURN
CONCATENATE (tMM, CONCATENATE ("",CONCATENATE (tD,CONCATENATE (
"",
CONCATENATE (
tH,
CONCATENATE ( "", CONCATENATE ( M, " Minutes " ) ) )
)
)))

 

// Seconds with leading zeros

// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
//return IF(MM=0,"",IF(MM>1,MM&" Months, ",MM&" Month, "))&IF(D=0,"",IF(D>1,D&" Days, ",D&" Day, "))&IF(H=0,"",IF(H>1,H&" Hours, ",H&" Hour, "))&IF(M<10,"0"&M,M)&" Minutes"))

//return IF(MM=0,"",IF(MM>1,MM&" Months, ",MM&" Month, "))

 

 

 

 

 

Replace "OC_Data,OC_Data[Minutes Logged by Technician]" with your data set of MINUTES, and this should sort it out for you 🙂
Let me know

Regards

Acyrus1992

 

 

 Hi there, 

 

I have a column with seconds that I need converted to HH:MM:SS. Can this be done using this code?

 

Many thanks in advance.

Hi @brianhackett5

 

Yeah it can be - dependant on your scenario.

What type of data filed is your seconds in?
Also - are you looking for hh:mm:ss or hhh:mm:ss  (ie - 24 hour clock [max: 23:59:59] or something 26:12:00 [ which would be 26 hours and 12 minutes] if you see what I mean?)

 

That would change how you modulate the code

It is in an excel file formatted as a decimal number. I've then converted it to duration and then total seconds in the BI query editor. 

 

The report is made up of journey durations. The end goal is to display the monthly driving time in the format HH:MM:SS if possible?

Just reading your reply again. It would be hhhh:mm:ss. 

 

Total company driving would be approx 5000hrs a month

 

Hi @brianhackett5

Could you send me a screen shot example of the data (seconds) and i'll create a solution based on your example using your table names, etc.

Driving Seconds.JPGTot Driving Seconds.JPG

 

 

The second photo is is the total driving seconds for August and part of July. 

Hi @brianhackett5

 

I've built you a solution: Replace   (TestTable,TestTable[DRIVING TIME])   with your tablename and seconds field

 

Let me know if this works for you 🙂


Use this:

 

measure =
VAR Duration = SUMX(TestTable,TestTable[DRIVING TIME])
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&" Hours, ",Hours&" Hour, "))
VAR tM = IF(Minutes=0,"",IF(Minutes>1,Minutes&" Minutes, ",Minutes&" Minute, "))
VAR tS = IF(Seconds=0,"",IF(Seconds>1,Seconds&" Seconds",Seconds&" Second"))

RETURN
CONCATENATE (th, CONCATENATE ("",CONCATENATE (tm,CONCATENATE ("", CONCATENATE (ts,"" )) )))

 

Just want to add my thanks for this, hunted high & low and couldn't find a working solution, until now, Thanks!

Hi Acyrus1992,

 

This works really well.

 

One thing though, it wont change when using slicers for different teams/drivers etc?

Hi Acyrus1992,

 

I know why it wasnt working; I was doing it as a column instead of a measure.

 

It works perfectly. Thank you so much for your help, I really appreciate it.

 

Have a noice weekend 🙂

No problem

 

Have a great one yourself 🙂

Hi Acyrus1992, 

 

Sorry to bother you again. 

 

Will your expression work for averages? See my expression below. 

 

It just returns the total value not the average. Is something to do with DISTINCTCOUNT?

 

Average = DIVIDE(SUM('Time @ Depot'[TIME AT DEPOT]), DISTINCTCOUNT('Time @ Depot'[ENTERING DATE]))

I see no issue with having an average included - but you need to apply the average at the sum level. 
Ie - calucalte the average before setting the Duration variable

If you get what I mean?

PS - was on holiday - so sorry about the delays

 

Hi all.

 

Hope it is ok that i reply in this thread.

 

I'm trying to use this code. and i've managed to make it show the duration/sum of the time from seconds to HHH:MM: SS

but the column is showing the sum for all rows in all rows.

 

Is there anyway that i can make it show the duration for every row and not a sum for all rows?

 

Thank you all in advance, you are helping alot!

 

Skärmklipp.PNG

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.