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

     

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!