Time and attendance calculation over multiple rows with variables.

[Anonymous]

Hi all, i am new to Power BI and this is my first question on here.

I am writing a time and attendance report and i need to do a calculation of the time between clock in and clock out.  However, the clock in and clock out are on seperate rows, there could be many users (workers) who have "clocked in" in that time and they could have clocked in and out multiple times per day.  So i am stuck and need your help.  

I have never posted a question before and i am not too sure how to attach my data.  So i have done it as a table below.

JournalRegistrationType = if it is a clock in or clock out

Worker Personnel Number = unique user ID

StartDateTime = the time of the clock in or out.

I would also need to add an additional section to this where if the AbsenceCodetimeAndAttendanceJobID = Dentist then the sum would be zero.  There could be a list of 20 or so different variables here. 

Thank you in advance for your help, really apprecite it.  Hope i have explained it well.  

ElectronicTimecardActivityRegistrationEntryNumber	AbsenceCodeTimeAndAttendanceJobId	TimeProfileDate	JournalRegistrationType	TimeAndAttendanceJobId	StartDateTime	WorkerPersonnelNumber
68719476736		21/11/2019	SignIn	Test-000145	21/11/2019 14:36:11 +00:00	50
68719478986		21/11/2019	SignOut	Test-000146	21/11/2019 14:36:12 +00:00	50
68719477486		21/11/2019	SignIn	Test-000145	21/11/2019 14:39:45 +00:00	25
68719478236		21/11/2019	SignOut	Test-000146	21/11/2019 14:39:46 +00:00	25
68719478237		22/11/2019	SignIn	Test-000145	22/11/2019 12:25:56 +00:00	25
68719478987		22/11/2019	SignOut	Test-000146	22/11/2019 12:25:57 +00:00	25
68719482031		25/11/2019	SignIn	Test-000145	25/11/2019 08:01:00 +00:00	191
68719483490		25/11/2019	SignIn	Test-000145	25/11/2019 08:01:00 +00:00	303
68719483491		25/11/2019	SignOut	Test-000146	25/11/2019 12:16:00 +00:00	303
68719482032		25/11/2019	SignOut	Test-000146	25/11/2019 12:16:00 +00:00	191
68719483492		25/11/2019	SignIn	Test-000145	25/11/2019 12:38:00 +00:00	303
68719482034		25/11/2019	SignIn	Test-000145	25/11/2019 12:38:00 +00:00	191
68719483493		25/11/2019	SignOut	Test-000146	25/11/2019 17:21:00 +00:00	303
68719482035		25/11/2019	SignOut	Test-000146	25/11/2019 17:21:00 +00:00	191
68719483494		26/11/2019	SignIn	Test-000145	26/11/2019 07:50:00 +00:00	303
68719482036		26/11/2019	SignIn	Test-000145	26/11/2019 08:02:00 +00:00	191
68719479736		26/11/2019	SignIn	Test-000145	26/11/2019 10:13:04 +00:00	2
68719478989		26/11/2019	SignIn	Test-000145	26/11/2019 10:57:26 +00:00	50
68719478988		26/11/2019	SignIn	Test-000145	26/11/2019 10:57:52 +00:00	25
68719478990		26/11/2019	SignIn	Test-000145	26/11/2019 11:06:00 +00:00	77
68719479738		26/11/2019	SignIn	Test-000145	26/11/2019 11:10:44 +00:00	103
68719492490		26/11/2019	SignOut	Test-000146	26/11/2019 12:30:00 +00:00	103
68719479745		26/11/2019	SignOut	Test-000146	26/11/2019 12:30:00 +00:00	25
68719479749		26/11/2019	SignOut	Test-000146	26/11/2019 12:30:00 +00:00	77
68719479743		26/11/2019	SignOut	Test-000146	26/11/2019 16:17:34 +00:00	2
68719483495		26/11/2019	SignOut	Test-000146	26/11/2019 17:12:00 +00:00	303
68719482037		26/11/2019	SignOut	Test-000146	26/11/2019 17:12:00 +00:00	191
68719479006		26/11/2019	SignOut	Test-000146	26/11/2019 17:30:21 +00:00	50
68719483496		27/11/2019	SignIn	Test-000145	27/11/2019 08:03:00 +00:00	303
68719482038		27/11/2019	SignIn	Test-000145	27/11/2019 08:03:00 +00:00	191
68719479744		27/11/2019	SignIn	Test-000145	27/11/2019 08:38:22 +00:00	2
68719479007		27/11/2019	SignIn	Test-000145	27/11/2019 09:44:15 +00:00	50
68719479746		27/11/2019	SignIn	Test-000145	27/11/2019 09:56:45 +00:00	25
68719479750		27/11/2019	SignIn	Test-000145	27/11/2019 10:08:31 +00:00	77
68719480486		27/11/2019	SignOut	Test-000146	27/11/2019 12:30:00 +00:00	25
68719483497		27/11/2019	SignOut	Test-000146	27/11/2019 12:51:00 +00:00	303
68719483498		27/11/2019	SignIn	Test-000145	27/11/2019 13:22:00 +00:00	303
68719481241		27/11/2019	SignOut	Test-000146	27/11/2019 16:19:23 +00:00	77
68719480489		27/11/2019	SignOut	Test-000146	27/11/2019 16:24:29 +00:00	50
68719481243		27/11/2019	SignOut	Test-000146	27/11/2019 16:31:52 +00:00	2
68719483499		27/11/2019	SignOut	Test-000146	27/11/2019 16:41:00 +00:00	303
68719482039		27/11/2019	SignOut	Test-000146	27/11/2019 16:47:00 +00:00	191
68719483500		28/11/2019	SignIn	Test-000145	28/11/2019 08:01:00 +00:00	303
68719482040		28/11/2019	SignIn	Test-000145	28/11/2019 08:02:00 +00:00	191
68719483501		28/11/2019	SignOut	Test-000146	28/11/2019 17:22:00 +00:00	303
68719482041		28/11/2019	SignOut	Test-000146	28/11/2019 17:42:00 +00:00	191
68719483502		29/11/2019	SignIn	Test-000145	29/11/2019 07:58:00 +00:00	303
68719482042		29/11/2019	SignIn	Test-000145	29/11/2019 08:04:00 +00:00	191
68719482043		29/11/2019	SignOut	Test-000146	29/11/2019 14:16:00 +00:00	191

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Anonymous]

Hi @v-lid-msft,

Apologies for the delay, i have been out for a while.

I managed to come up with a solution which works for multiple areas, however, based on a new addition there is now a variable that has me stuck. 

Really appreciate your help, that has helped on other reports i am writing.

Thank you,

Steven

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

TotolMinutes = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[WorkerPersonnelNumber],
        "Total Time",
        SUMX (
            'Table',
            IF (
                [JournalRegistrationType] = "SignOut",
                VAR ti = [StartDateTime]
                RETURN
                    DATEDIFF (
                        CALCULATE (
                            MAX ( 'Table'[StartDateTime] ),
                            FILTER (
                                'Table',
                                'Table'[JournalRegistrationType] = "SignIn"
                                    && 'Table'[StartDateTime] < ti
                            )
                        ),
                        ti,
                        MINUTE
                    ),
                BLANK ()
            )
        )
    ),
    [Total Time]
)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.

Best regards,