Forum Discussion
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...
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
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
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.