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,
I am working on a report which calculates and determines the shifts of our employees by their clock in and clock out.
The data I had was limited to employees' in/out time and date.
I had managed to use some functions to determine things like their shifts, if they were late, if they exit work earlier, if the exit later than their work time.
Counting the shifts I try to calculate the number of shift for a period or total shifts as days worked.
But there's a problem which makes nearly all my efforts and report deceiveful.
Some people frequently clock out twice and that makes them have 2 shifts for a day.
Here are some examples of the dataset:
Here's the DAX formula I use to calculate the number of shifts.
Number of regular shifts =
CALCULATE(COUNTA(EventRecordOUT[Shift]);
OR(EventRecordOUT[Shift] = "Regular -15 min.";
OR(EventRecordOUT[Shift] = "Regular";
OR(EventRecordOUT[Shift] = "Regular +30 min.";
EventRecordOUT[Shift] = "Regular +60 min."))))
So would you please help me by telling me or giving me idea how to make it that the formula counts only 1 shift per day.
Thanks in advance!
Solved! Go to Solution.
Number of regular shifts ALT = CALCULATE ( DISTINCTCOUNT ( EventRecordOUT[Date] ); FILTER ( EventRecordOUT; EventRecordOUT[Shift] = "Regular -15 min." || EventRecordOUT[Shift] = "Regular" || EventRecordOUT[Shift] = "Regular +30 min." || EventRecordOUT[Shift] = "Regular +60 min." ) )
Hope this helps.
Good Luck!
EDIT: Depending on how you actually display this you may have to change the Measure a bit to get the correct Total
So I created a Test Column
Test Column = [Employee]&" - "&[Date]
And then Modified the above Measure like this...
Number of Regular Shifts NEW = IF ( HASONEVALUE ( EventRecordOUT[Date] ); CALCULATE ( DISTINCTCOUNT ( 'EventRecordOUT'[Date] ); FILTER ( 'EventRecordOUT'; 'EventRecordOUT'[Shift] = "Regular -15 min." || 'EventRecordOUT'[Shift] = "Regular" || EventRecordOUT[Shift] = "Regular +30 min." || EventRecordOUT[Shift] = "Regular +60 min." ) ); CALCULATE ( DISTINCTCOUNT ( EventRecordOUT[Test Column] ); FILTER ( 'EventRecordOUT'; 'EventRecordOUT'[Shift] = "Regular -15 min." || 'EventRecordOUT'[Shift] = "Regular" || EventRecordOUT[Shift] = "Regular +30 min." || EventRecordOUT[Shift] = "Regular +60 min." ) ) )
As I said the condition plus the calculation may have to be changed depending on how you visualize it...
This should hopefully give you some ideas.
Good Luck!
Hi @Birdjo,
if you don't need these duplicate rows, then just remove them from your dataset. Then you don't have to solve it in your DAX formula.
Regards.
Pavel
Hi @PavelR,
Thank you very much for your response. I think it is a clever and simple solution.
But we don't have only 1 employee, we have nearly 300 employees and if I remove the duplicates of the date what will happen is we will have only 1 employee entry or exit.
Do you know how to remove the duplicates for date for employee. So we can keep the employees but remove only their duplicate dates?
Kind regards,
Birdjo
Yeah @Birdjo, I exactly get it what you mean.
I have created a sample (only few rows for a test) and I think I know how to solve it.
I would try to create new column which will merge the whole Date column and just hours from Time column. Then select this new column and Employee column and remove duplicates (Home -> Reduce rows -> Remove duplicates)
Please test it on your much bigger dataset and let me know, if it helps.
Regards.
Pavel
I already have Date and time column. It is showing down to seconds. So it is impossible to clock in in the same second. Usually they clock in with two seconds difference.
Many employees enter in the same minute. If that wasn't the case we could have changed the date and time format to dd/mm/yyy hh/mm (without ss) and remove the duplicates. But many employees enter in the same minute.
Number of regular shifts ALT = CALCULATE ( DISTINCTCOUNT ( EventRecordOUT[Date] ); FILTER ( EventRecordOUT; EventRecordOUT[Shift] = "Regular -15 min." || EventRecordOUT[Shift] = "Regular" || EventRecordOUT[Shift] = "Regular +30 min." || EventRecordOUT[Shift] = "Regular +60 min." ) )
Hope this helps.
Good Luck!
EDIT: Depending on how you actually display this you may have to change the Measure a bit to get the correct Total
So I created a Test Column
Test Column = [Employee]&" - "&[Date]
And then Modified the above Measure like this...
Number of Regular Shifts NEW = IF ( HASONEVALUE ( EventRecordOUT[Date] ); CALCULATE ( DISTINCTCOUNT ( 'EventRecordOUT'[Date] ); FILTER ( 'EventRecordOUT'; 'EventRecordOUT'[Shift] = "Regular -15 min." || 'EventRecordOUT'[Shift] = "Regular" || EventRecordOUT[Shift] = "Regular +30 min." || EventRecordOUT[Shift] = "Regular +60 min." ) ); CALCULATE ( DISTINCTCOUNT ( EventRecordOUT[Test Column] ); FILTER ( 'EventRecordOUT'; 'EventRecordOUT'[Shift] = "Regular -15 min." || 'EventRecordOUT'[Shift] = "Regular" || EventRecordOUT[Shift] = "Regular +30 min." || EventRecordOUT[Shift] = "Regular +60 min." ) ) )
As I said the condition plus the calculation may have to be changed depending on how you visualize it...
This should hopefully give you some ideas.
Good Luck!
I am having a similar issue.
Im trying to return a 1 per employee per day, but a 0 if their name appears multiple times within the same day.
Attached is an example of how I would like it to appear within PowerBI.
Im so stuck, I dont even know where to begin.
This calculated column can give me even more analytical opportunities. Thank you very much!
Thank you very much @Sean That solved the problem. I didn't have the time to test it for all of the shifts but I will apply it tomorrow for all of them.
Thanks again!
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |