Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
This is the new calculated column I need:
This is the table I am trying to generate in PBI Desktop:
Thank you in advance.
Solved! Go to 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
Proud to be a PBI Community Champion
@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
Proud to be a PBI Community Champion
Thanks PattemManohar, it is working brilliantly!
Hi,
you can create a summary table with CARDHOLDERID, EVENTDATE, SHIFT, SUM(ONSITEHOURS).
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
95 | |
83 | |
70 | |
67 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |