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 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.
| 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
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:
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:
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:
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!