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:   A manufactur...
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

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.