cancel
Showing results for
Did you mean:
Highlighted
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]?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Continued Contributor

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

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

Highlighted
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)```

36 REPLIES 36
Highlighted
Continued Contributor

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

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

Highlighted
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

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

Highlighted
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)```

Highlighted
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

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

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

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

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:

_________________________________________________________________________________

Ideally I'd like to show this:

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

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_error

Announcements

#### Power Platform Community Conference

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

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021