topic Re: Sum of duration times in DAX Commands and Tips

Sum of duration times

Littleconfused — Wed, 01 Feb 2023 04:53:03 GMT

Very new to Power BI. I have two times, one being entry time and one being exit time. I need to show the total time spent HH:MM for each entry with a total at the bottom of that column. I am aware that you are unable to sum string or text so have two options, looking ofr the best solution. Column [Sum of Duration] is the difference between the two columns in minutes only and column [Duration Times] is the conversion from the data uploaded from the power query that transfers in as decimal time.

Below is formula used to convert decimal time to hrs and minutes but comes in as a string.

Looking for any solution or if you have a better way to get this done, please help

Re: Sum of duration times

tamerj1 — Wed, 01 Feb 2023 08:15:55 GMT

Hi Littleconfused
Please refer tattached sample file with the proposed solution

Duration Times = VAR FirstDay = MIN ( 'Table'[EntryDateTime] ) VAR LastDay = MAX ( 'Table'[EntryDateTime] ) VAR Days = DATEDIFF ( FirstDay, LastDay, DAY ) VAR TimeDuration = FORMAT ( SUMX ( 'Table', 'Table'[ExitDateTime] - 'Table'[EntryDateTime] ), "HH:MM" ) RETURN IF ( HASONEVALUE ( 'Table'[EntryDateTime] ), TimeDuration, Days & " Days & " & TimeDuration )

Re: Sum of duration times

Littleconfused — Thu, 02 Feb 2023 01:27:26 GMT

Hi tamerj1

I copied your formula, below screenshot of mine however i have obviously done something wrong as i get the same number at the end of each line and no total at the bottom.

Re: Sum of duration times

tamerj1 — Thu, 02 Feb 2023 02:50:10 GMT

Littleconfused

Yea because you have duplicate records. To bbe more specific, you have records that have the same start and end times. I broke that by adding an index column as I don't know what other columns you have. Please explain the meaning of these duplicate records and how would you like to calculate the time in this case.

Re: Sum of duration times

Littleconfused — Thu, 02 Feb 2023 02:57:56 GMT

the data is entry times and dates and exit times and dates into a business over the last 18 months. We want to be able to select a contractor and see when he has been onsite over this period of time, select a date and see who was on site on that day, select a person and see when they were onsite. With all of the above we want to be able to see total hours as per our selection. We will have contractors onsite for the same period of time due them entering and exiting together.

Re: Sum of duration times

tamerj1 — Thu, 02 Feb 2023 03:28:13 GMT

Littleconfused

I'm also dealing with attendance data for our contractors in my company and of course I have similar situation of people entering and leaving at the same time. What do you get when you select one person?

Re: Sum of duration times

Littleconfused — Thu, 02 Feb 2023 03:36:26 GMT

When i filter to one person i get the below. To the left of this i have the name of the person and the company they work for.

I have also created the below where i can select by month or week or day and see who is onsite. I just cant get the total to work.

Re: Sum of duration times

tamerj1 — Thu, 02 Feb 2023 04:12:53 GMT

Littleconfused

Is there any chance that the start and end times columns that you are using in this table are coming from a different (perhaps redundant) table other than Query1? I'm saying that because the result is impossible to get otherwise and because it happens with me before given that the file might contain multiple redundant queries for whatever reasons.

Re: Sum of duration times

Littleconfused — Thu, 02 Feb 2023 04:50:58 GMT

I dont belive ti is as i have only one source that i am getting the data from. I have just checked that and it is still only from one source

Re: Sum of duration times

tamerj1 — Thu, 02 Feb 2023 08:19:13 GMT

Littleconfused

Can you share the sample file that you're working on?

Re: Sum of duration times

Littleconfused — Thu, 02 Feb 2023 22:51:38 GMT

It is avery basic file as below.