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