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.
Hello Community -
I had a post on this prior and got a solution, but there is one thing that is throwing this off. Here is the Original Post
Below is the current code I am using to calculate the new Hours logged.
Hours Logged Corrected =
VAR CurrentStart = '2 - CW - TimeEntry'[timeStart]
VAR CurrentEnd = '2 - CW - TimeEntry'[timeEnd]
VAR CurrentEmpolyeeTable =
CALCULATETABLE (
'2 - CW - TimeEntry',
ALLEXCEPT ( '2 - CW - TimeEntry', '2 - CW - TimeEntry'[member_name], '2 - CW - TimeEntry'[Start Date] )
)
VAR PreviousRecords = FILTER ( CurrentEmpolyeeTable, '2 - CW - TimeEntry'[timeStart] < CurrentStart )
VAR PreviousStart = MINX ( PreviousRecords, '2 - CW - TimeEntry'[timeStart] )
VAR PreviousEnd = MAXX ( PreviousRecords, '2 - CW - TimeEntry'[timeEnd] )
VAR Starting = MAX ( CurrentStart, PreviousEnd )
VAR Ending = MAX ( CurrentEnd, PreviousEnd )
RETURN
DATEDIFF ( Starting, Ending, SECOND ) / 3600
But the one item that is causing issues is if there are time entries that have the exact same starttime. If the have the same start time, there are not counting overlap hours for some reason. See the screen shot below, on the first 2 lines in the table, one of those entries should have a 20 min (0.33 hour) overlap. But they both show 0.00 overlap hours.
I have tried several things to tweak the formula to account for the exact same starttime across entries, but nothing has worked.
Looking for some quick help as my client has this as an urgent request for payroll processing.
Any help from the community is appreciated!
Thank You
Ryan F
Not sure if this will help or not but I noticed the previous records variable was only looking for entries that were less than the current start so it would never find a same start time.
To solve that I created an index column based on concatenating startTime, endTime and ticketID to prevent duplicates and then adjusted the existing code to look for previous values based on the index number.
Index Calculated Column:
Proud to be a Super User! | |
Thanks @jgeddes for the suggestion. I do not think that is going to work. Tickets are not in any specific order, so members can back date time causing ticket numbers for prior days to be a higher number than tickets for today. I tried to test it out to see if I was wrong and it would work, but performance on it is very slow and I cannot get the new column to populate.
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |