Overtime Report Help

[AppleMan]

Hi,

I need some help designing the best way to make an overtime report work.

Right now, I have a table that has every labor entry employees have made. The important columns are:
Employee number, date, and hours (among some others).

I need to find a way to show overtime hours (which for this question lets just say is anything over 40 hours in a week, other tweaks will need to be made but I will figure out that problem as I go). 

What is the best way to sum hours by week and find when an employee id who may have 20 entries that need to be summed in any given week, is over the 40 hour threshhold?

I can give further criteria and information as needed, just need a hand figuring out the best logic to handle what I need. 

Thanks!

[rsbin]

@AppleMan ,

First thing I would do is add a Calendar or Date Table if you have not already done so.  This is a PBI best practice and numerous videos are available to be had.
Once you have a proper Date Table, you will then need to define when your PayWeek starts.  Do you use Sun to Sat, Mon-Sun, or some other combination.  Here is a small data sample.

Join this Date Table to your Fact Table (Labor entries) via the Date Column.  You will then be able to Sum your Hours column by your defined PayWeek. Then create a measure or calculated column to determine OT.

Overtime = SumofHours - 40
Hope this gets you started.  I will continue to try to assist if you hit any hurdles along the way.

Regards,

[AppleMan]

I indeed do have a date table, always use one. Did not think of adding a column for the first day of the week, very smart. 
This is where things get a little tricky. For employees working Mon-Fri, the threshhold for overtime is 40 hours. Simple enough.
However, for employees working Sat/Sun, or specifically noted by a shift code I have in my table of hours, their threshhold is anything worked over 8 hours in a day is overtime. 

So my thoughts were it would be easiest to find the overtime of the normal week (Mon-Fri), then the overtime of the weekend shift workers, and add the two together as a final step to get the total weeks overtime (Sun-Sat). I planned to do this in a measure ran by variables. 

I think I can do this by summing the pay hours by week, mon-fri, then summing pay hours for that same period where the shift code is the weekend code. Then add the two together. Does that sound logical? Any flaws or concerns as I size up this report and its capabilities?

[rsbin]

@AppleMan ,

Believe your logic will work. 
Personally, I would start by creating another Calculated Column called something like "Weekend OT" so I can clearly see if the Total Measures are working properly.  Once you are confident this is working accurately, then create the Measures as you have described.

Best Regards,

[AppleMan]

Are you saying you would make a column that is basically running the measure and totalling the "Weekend OT" over time to see if it is including and excluding what it should be? 

[rsbin]

@AppleMan ,

Apologies for any confusion.  Here is a sample of what I had in mind. 
Weekday OT_Total = 40 - SumofHours. (This is 2 in my sample below)

WeekendOT (calc column) = [Hours] - 8
WeekendOT_Total = SUM( Table[WeekendOT] ) = 6

Name	Date	WeekStart	Hours	Weekend OT
John	5-Feb	5-Feb	8.00
John	6-Feb	5-Feb	9.00
John	7-Feb	5-Feb	9.00
John	8-Feb	5-Feb	8.00
John	9-Feb	5-Feb	8.00
Jane	10-Feb	5-Feb	10.00	2
Jane	11-Feb	5-Feb	12.00	4

I'm thinking a couple of steps ahead, as it will probably be useful to your Users (Mgmt) to differentiate between Weekday and Weekend OT (just based on my experience).

Hope this helps...and again, did not mean to confuse the situation.

Regards,

[AppleMan]

I just looked at my data and unfortunately it wont be this easy. There can be multiple hour records on the same day for these weekend shifts. So before I take the 8 hours off of them they need to be summed. 

For example:

Name	Date	WeekStart	Hours	Weekend OT
Jane	10-Feb	5-Feb	3.69
Jane	10-Feb	5-Feb	4.41
Jane	10-Feb	5-Feb	2.51

It coud look like this. I would think at this point I need to do some kind of summarized table inside a measure that summs each entry together for each day first. Thoughts?

[rsbin]

@AppleMan ,

Please review attache pbix file.
Have created a dummy fact table premised on your example.
Created a series of Measures.  I am sure these can be written in a more succint manner, but I wanted to be clear in my methodology.  You can modify as you wish.
Please review tomorrow and if there is anything you are unclear about, please let me know.

Have a great evening!

[AppleMan]

I appreciate your help. I have one last question for you:

I have my date table, and I have a table that users will be entering holiday dates on. I need to find a way to have the date table have a holiday column, which shows 1 if that date is a holiday, and 0 if that day is not, based on the holiday table users will enter dates in. 

For example, if 1/30 is a holiday at our company, they would enter it in the holiday table. The date table in BI would then show a 1 in the holiday column on the 1/30 date. 

I have played around with a few ways to do this but have not got it working how I want. What is the best way to have a custom column that looks at another table like this?

[rsbin · Accepted solution]

@AppleMan ,

Various ways this can be done.  I have attached an updated pbix that shows one of these ways.

Import your Holiday Table.  Connect it to your Date Table.  It should be 1-1 relationship.

Then I created a Calculated Column in your Date Table using SWITCH and LOOKUPVALUE.

I structured it to give you 0's and 1's, but you can replace these with other values if you so choose ("Holiday" or "Work Day").

Let me know if you run into any issues....

Regards and Good Luck

[AppleMan]

That works perfectly, and I appreciate the quick answer. I could probably poke your brain on this project for another week but I will mark this is the solution so you get your credit!

[rsbin]

@AppleMan ,

Yes please close this thread.  Continue to poke away.  You caught me at a good time, because I have just published an Employee Time Report for my company.
Best Regards,

[AppleMan]

Hi! 

Happy Friday!

I need help with hopefully one last thing. My OT is calculating exactly how I want, except for employees who work the "weekend" shift where OT is calculated daily when they go over 8 hours. It works fine for a single employee however I am testing out my measure to make sure it works for multiple employees and running into an issue. 

This is what runs my OT calculation. If you look in the _OvertimeWeekend variable, youll notice that it multiplies the end part by 3. This is just temporary since the weekend overtime employee im testing with had "weekend" shifts in 3 days, or to be more clear he worked on 3 seperate days where on each day if he had more than 8 hours summed it was considered overtime.

I am trying to think of a good way to have this measure accomplish what I want and I have not been able to. I will give you an example:

Jim	1/28	11.05	Weekend
Jim	2/2	8.65	Weekend
JIm	2/3	5.08	Weekend
Bob	1/29	7.5	Weekend
Bob	1/29	1.5	Weekend

In this example, I would need that _OvertimeWeekend variable to return for Jim, (11.05 - 8 = 3.05) + (8.65 - 8 = .65) = 3.7 for overtime hours. For Bob, return (7.5 + 1.5) - 8 = 1 hours for overtime. If I had a card showing the total for this week range it would return 4.7 hours overtime. 

The issue I am running into in my measure is dynamically getting that 3 I mentioned before to instead change to the number of days someone has hours, which works perfect for Jim, but would also throw Bobs overtime hours off since his would multiply by 3 and he worked only a single day. If I can figure out how to get these weekend, or shift 41 as you can see in my measure, workers overtime to calculate correctly then the rest of this report should go smoothly. 

If you see any flaws with how im calculating this in general I am happy to take critism.

I appreciate any help, thank you!

[rsbin]

@AppleMan ,

Unable to examine this in any detail until Monday, but if you look at the file I sent you, I calculate OT separately for Saturday and Sunday in the WeekendOT measure

WeekendOT_Total = 
VAR _SatHours = CALCULATE( SUM(AMTable[Hours] ),
                        ALLEXCEPT( DIM_Date, DIM_Date[Date] ),
                        DIM_Date[DayName] = "Saturday" )
VAR _SatOT = IF( _SatHours > 8, _SatHours - 8, 0 )    //Calculate OT Hours                        
VAR  _SunHours = CALCULATE( SUM(AMTable[Hours] ),
                        ALLEXCEPT( DIM_Date, DIM_Date[Date] ),
                        DIM_Date[DayName] = "Sunday" ) 
VAR _SunOT = IF( _SunHours > 8, _SunHours - 8, 0 )    //Calculate OT Hours
RETURN
    _SatOT + _SunOT

This ensures that OT is calculated only if employee works greater than 8 hours on a specific day.

I think this is where you are indicating troubles.  Replace your _Overtime Weekend variable with my variation and see if that works towards solving your issue.

If not, we will examine in more detail next week.

Enjoy the weekend!

[AppleMan]

I did see the way you did it, the only issue is mine is not going specifically by saturday/sunday. It just goes off an employee clocking in to shift "41". This means ive seen it fall on Mondays, tuesdays, etc, dont ask me how that is possible lol.

I will continue to troubleshoot till the end of the day here on a solution that may work for my data. 

[rsbin]

@AppleMan ,

Good Morning.  In this case, my approach would be to create a new Calculated Table which creates a summary of your Shift 41 (Weekend) Hours.  Group by Employee, Group by Date and Sum Hours so you get a Total for each Employee for each separate day.  So your above sample table above would be 4 records and not 5.  Then create a new Calculated Column to get OT as I described in one of my earlier posts above.  Then you have a way to easily get your Total Weekend OT.
After you get this working then you can spend time to get your other formula above working properly.  But I think it would entail creating a virtual table within your VAR _OvertimeWeekend, which takes some practice to get working properly and to account for various scenarios.

Hope you understand this approach and it is to your liking.

Best Regards,

[AppleMan]

My summary table is working excellently. Groups exactly how you described and I also added a custom column that calculates the overtime for each employee. Now I am going to see if I can implement this into the above measure I shared to accurately get all overtime for employees, this should be the tricky part, using virtual tables. 

[rsbin]

@AppleMan ,

You don't need to incorporate this into your Measure above.
Just create a simple measure called WeekendOT = SUM( YourOTTable[OTHours] ).

Your first measure is WeekdayOT.

Then for Overtime_Total = [WeekdayOT] + Weekend[OT]

The idea behind creating the calculated table was to avoid messing with your Variables.

Hope I have explained this properly.

[AppleMan]

I got a question for you in line with the switch statement you helped me with for holidays the other day. Right now, it will display a 1 on the day of the holiday. So for example, if 1/30 was a holiday, in the date table the holiday column will have a 1 on that day. I want to add another column to my date table called "Holiday Week", which displays a 1 not only on 1/30, but all days of that week that the holiday falls on. So 1/28 - 2/3 would have a 1 for this column. 

Is there a simple way to adapt the switch statement to do this, or a better way?

[rsbin]

@AppleMan ,

Please try this as a Calculated Column in your Date table

HolidayWeek = SWITCH(
                  TRUE(),
                  CALCULATE( SUM( DIM_Date[CompanyHolidays] ), 
                     ALLEXCEPT( DIM_Date, DIM_Date[Year-WeekNumber_Sunday] )) = 1, 1,
                  0 )   

 You can switch out the column [Year-WeekNumber_Sunday] to your relevant column.  Could be your [WeekStart_Sun] column.
I made a SWITCH statement to account for those weeks that may have 2 holidays (i.e. US Thanksgiving).  If you don't care for this, you can just use the CALCULATE statement.  Any weeks containing a Holiday will be > 0.

DateKey	Date	Year-WeekNumber_Sunday	PayWeekStart_Sun	PayWeekEnd_Sat	CompanyHolidays	HolidayWeek
20240706	07/06/2024	2024-27	06/30/2024	07/06/2024	0	1
20240705	07/05/2024	2024-27	06/30/2024	07/06/2024	0	1
20240704	07/04/2024	2024-27	06/30/2024	07/06/2024	1	1
20240703	07/03/2024	2024-27	06/30/2024	07/06/2024	0	1
20240702	07/02/2024	2024-27	06/30/2024	07/06/2024	0	1
20240701	07/01/2024	2024-27	06/30/2024	07/06/2024	0	1
20240630	06/30/2024	2024-27	06/30/2024	07/06/2024	0	1
20240203	02/03/2024	2024-05	01/28/2024	02/03/2024	0	1
20240202	02/02/2024	2024-05	01/28/2024	02/03/2024	0	1
20240201	02/01/2024	2024-05	01/28/2024	02/03/2024	0	1
20240131	01/31/2024	2024-05	01/28/2024	02/03/2024	0	1
20240130	01/30/2024	2024-05	01/28/2024	02/03/2024	1	1
20240129	01/29/2024	2024-05	01/28/2024	02/03/2024	0	1
20240128	01/28/2024	2024-05	01/28/2024	02/03/2024	0	1

Hope this works for you

[AppleMan]

I changed the original Holiday column to text, and altered the original switch statement to display the name of the holiday rather than 1 as below:

So to make the switch statement for HolidayWeek work I changed it from summing that column to counting it (looking for holiday count above 0). 

However I must not fully understand this logic here, since it is not working as intended: