cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

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

Accepted Solutions
Highlighted
Continued Contributor
Continued Contributor

Re: Sum of duration of task with Days, Hours, Minutes

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

Highlighted
Microsoft
Microsoft

Re: Sum of duration of task with Days, Hours, Minutes

@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

36 REPLIES 36
Highlighted
Continued Contributor
Continued Contributor

Re: Sum of duration of task with Days, Hours, Minutes

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

Highlighted
Post Patron
Post Patron

Re: Sum of duration of task with Days, Hours, Minutes

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?

Highlighted
Continued Contributor
Continued Contributor

Re: Sum of duration of task with Days, Hours, Minutes

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
Highlighted
Microsoft
Microsoft

Re: Sum of duration of task with Days, Hours, Minutes

@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

Highlighted
Post Patron
Post Patron

Re: Sum of duration of task with Days, Hours, Minutes

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

 

Highlighted
Microsoft
Microsoft

Re: Sum of duration of task with Days, Hours, Minutes

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

 

Highlighted
Post Patron
Post Patron

Re: Sum of duration of task with Days, Hours, Minutes

@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

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

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

 

Highlighted
Anonymous
Not applicable

Re: Sum of duration of task with Days, Hours, Minutes

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors