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 ,

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!

[AppleMan]

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".

[AppleMan]

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:

[rsbin]

@AppleMan ,

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

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

[AppleMan]

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:

[rsbin]

@AppleMan ,

Does this work for you?

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

FullWeeknumHoursThreshold

317	41	32
319	5	24

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

Regards,

[AppleMan]

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. 

[rsbin]

@AppleMan ,

Column Name from my dummy table:  TestThreshold[FullWeekNum]

[AppleMan]

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! 

[AppleMan]

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. 

[AppleMan]

I will for sure let you know if I run into any further questions! I should have the data manipulated how I want from here, just need to make sure my measures function how I expect as I get the visuals thrown together. 

Again, appreciate the help!