Overtime Report Help

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. 

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,

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. 

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.

Yeah thats how I ended up doing it. Have a few more kinks to work out to get this working properly with a holiday schedule table I have to change the overtime threshhold, but all in all its coming together well. 

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?

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.

Hope this works for you

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:

AppleMan ,

Change "COUNT" to "COUNTA".  This is how DAX counts text values.

I suggest to change your Condition from "=1" to ">=1"  to take into account weeks having more than 1 holiday unless you are absolutely sure there is no week having more than 1 holiday

Hope this works!

I planned to change it to >=1 for just that reason, but COUNTA partially did it! I knew thats how that worked too.. cant you tell its Monday.

The issue im seeing is this calculated column is throwing a 1 for every single row currently.

Only days/weeks with holidays:

Column calculation for verification:

AppleMan ,

In your Calculated Column [Holiday] column, change "0" to Blank().

PBI is reading your "0" text value and counting it as 1.

You can ignore my last response, I realized the fault in my logic is it is counting the text field and since the "0"'s are considered text it was always above 0. I added a clause in the calculate function to exclude any lines that have a holiday of "0".

I apologize for bombarding you with a million questions, but I have another one if you dont mind helping.

I set up that field we spoke about previously in the date table to give the number of holidays in that week, or 0 if none, like below:

I have decided to tally all overtime in seperate summarized tables so that I can just sum the columns necessary. What I need to do is have the overtime threshold adjust itself properly based on holiday number in that week, so week 317 would have a threshhold of 24 (40 - (2 holidays * 8)), and week 319 should have a threshhold of 32 respectively. 

What is the best way to write this dax formula to adjust according to what week the data falls in?

Overall table structure in case its helpful:

AppleMan ,

Does this work for you?

Threshold = 40 - 
            ( 8 * LOOKUPVALUE( TestDate[HolidayWeek], TestDate[FullWeeknum], [FullWeeknum] ))

FullWeeknumHoursThreshold

My test data as follows:  Week 317 has 1 holiday, Week 319 has 2 holidays.

Regards,

What is the final variable '[fullweeknum]' in reference to? 

I am not able to try this formula as its typed. I can enter everything as you show except that last variable to test it. 

AppleMan ,

Column Name from my dummy table:  TestThreshold[FullWeekNum]

I found my issue, I still had the whole function wrapped in calculate. I removed that and this seems to work how I want! Thank you!