Forum Discussion
Overtime Report Help
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
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.
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:
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.
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!