Sum of duration times

[Littleconfused]

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

[Littleconfused]

It is avery basic file as below.

[Littleconfused]

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. 

[Littleconfused]

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.

[tamerj1]

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

[Littleconfused]

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.

[tamerj1]

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

[Littleconfused]

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

[tamerj1]

@Littleconfused 

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

[tamerj1]

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

[tamerj1]

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
    )