Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need Help Calculating Offsite Hours

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.

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks PattemManohar, it is working brilliantly!