We have a production floor report that essentially takes timeclock data and compares it to set standard minutes to generate efficiency ratings per employee.
The timeclock data is manually imported via SQL every morning as our timeclock system is 3rd party and they email us XLS files daily, there is no direct access to their database. Not only is this process not very reliable, but when employees miss punches (happens all the time) we have to manually go update the record to a default 8/10 hours (shift dependent) of time.
In efforts to get rid of this timeclock importing process, I have come up with a measure that basically just estimates the minutes at 480/600 shift respective. This works well when looking at the report for a given day since most employees are within 10-15 minutes of this time anyway and we dont have to deal with missed punches, but.... as soon as you try to view an aggregate of time like a month, the efficienices are all out of whack because its not summing the calculated minutes for that given time.
Here is my measure DAX: IF(MAX(Events[Date]) = TODAY(),SUMX(EmplTableAX, SWITCH(EmplTableAX[Shift], 1, MIN( 480, 24*60*(NOW() - (TODAY() + TIME(5,0,0)) )), 2, MIN( 600, 24*60*(NOW() - (TODAY() + TIME(13,45,0)))))),SUMX(EmplTableAX, SWITCH(EmplTableAX[Shift],1,480,2,600)))
Kind of tough to read, but essentially its figuring out the amount of minutes passed for the current day (so the efficiency value throughout the current day is valid), otherwise for all historical days its figuring out what shift the employee is (1st or 2nd) and setting the value to 480/600 respectively.
Any assistance is appreciated and let me know if you need more context or details. It can be tough to figure these specific issues out.
