Need Help Calculating Offsite Hours

[Anonymous]

Hi PBI Community,

This is my first post! How do I create a calculated column that calculates employee offsite hours using gate entry and exit data.

Here are some conditions:

1. A manufacturing site has a turnstyle gate that records all employee Entry and Exit times each day using an RFID card reader.
2. The manufacturing site operates 24/7.
3. There are 2 shifts per day.
   1. AM shift operates from 0600 to 1800.
   2. PM shift operates from 1800 to 0600 (the following day).
4. Each employee generally works a 12 hr shift each day.
5. In a perfect word each employee will have 1 entry and 1 exit per day and work a total of 12hrs.

I need to calculate the total offsite hours per day if an employee leaves the site part way through the day and returns before leaving at the end of their shift. 

 This is what the data looks like now:

Sample Source Data

This is the new calculated column I need:

Required Output

This is the table I am trying to generate in PBI Desktop:

Goal

Thank you in advance.

[PattemManohar · Accepted solution]

@Anonymous Please try as below:

Create a supporting field "Rnk" as 

Rnk = RANKX(FILTER(Test183WorkingHours,Test183WorkingHours[CardholderID]=EARLIER(Test183WorkingHours[CardholderID])),Test183WorkingHours[EventTime],,ASC)

Then create "OffsiteHrs" field as

OffsiteHrs = 
VAR _CurrVal = Test183WorkingHours[EventTime]
VAR _PrevVal = LOOKUPVALUE(Test183WorkingHours[EventTime],Test183WorkingHours[CardholderID],Test183WorkingHours[CardholderID],Test183WorkingHours[Rnk],Test183WorkingHours[Rnk]-1)
VAR _Result = IF(Test183WorkingHours[EntryExit]="Entry",DATEDIFF(_PrevVal,_CurrVal,HOUR),0)
RETURN IF(ISBLANK(_Result),0,_Result)

Then the output will look like

[Anonymous]

Thanks PattemManohar, it is working brilliantly!

[Anonymous]

Hi,

you can create a summary table with CARDHOLDERID, EVENTDATE, SHIFT, SUM(ONSITEHOURS).

Ex: SUMMARY = SUMMARIZE('TableName', [CARDHOLDERID], [EVENTDATE], [SHIFT], "ONSITEHOURS", SUM(]ONSITEHOURS]))

Note: Change the datetime to DATE.

Then add a column to substract 12-sum(ONSITEHOURS).

Thanks,

Satya

[Anonymous]

Thanks ASN_SATYA, but how do I group the night shift records? Currently, the records are being split because the end of shift falls on the next day, and as a result the OffsiteHours can be incorrectly calculated when it is a Night shift.

 

[Anonymous]

Hi Jay, i dont say its a good solution... but a patch work

--> We need to add a column with condition

if shift = Night and time is > Midnight and < 18:00 (or Noon) ==> Date = Date-1, else Date = Date

I couldnt think of any other solution...

Thanks,

Satya

[Anonymous]

Hi ASN_SATYA, any solution is better than no solution which is where I was at before. I appreciate your previous post. It has helped me. Thank you.