Forum Discussion
Solved
Bi-Weekly Calendar
Good Afternoon,
Haven't been able to come up with a solution to this one. Have a Calendar Table and need to add a Calculated Column to get a Bi-Weekly Payroll Date that starts on Monday.
| Date | StartDate_Mon |
| 12/25/2023 | 12/25/2023 |
| 12/26/2023 | 12/25/2023 |
| 12/27/2023 | 12/25/2023 |
| 12/28/2023 | 12/25/2023 |
| 12/29/2023 | 12/25/2023 |
| 12/30/2023 | 12/25/2023 |
| 12/31/2023 | 12/25/2023 |
| 1/1/2024 | 12/25/2023 |
| 1/2/2024 | 12/25/2023 |
| 1/3/2024 | 12/25/2023 |
| 1/4/2024 | 12/25/2023 |
| 1/5/2024 | 12/25/2023 |
| 1/6/2024 | 12/25/2023 |
| 1/7/2024 | 12/25/2023 |
| 1/8/2024 | 1/8/2024 |
| 1/9/2024 | 1/8/2024 |
| 1/10/2024 | 1/8/2024 |
| 1/11/2024 | 1/8/2024 |
| 1/12/2024 | 1/8/2024 |
| 1/13/2024 | 1/8/2024 |
| 1/14/2024 | 1/8/2024 |
| 1/15/2024 | 1/8/2024 |
| 1/16/2024 | 1/8/2024 |
| 1/17/2024 | 1/8/2024 |
| 1/18/2024 | 1/8/2024 |
| 1/19/2024 | 1/8/2024 |
| 1/20/2024 | 1/8/2024 |
| 1/21/2024 | 1/8/2024 |
| 1/22/2024 |
1/22/2024 |
Kind Regards,
try this code, it assigns the dates correctly for 2022-2025, could probably be derived from nr of days in the first/last week of the year
Payroll Date = VAR _year_end_offset_map = { ( 2022, 1 ), ( 2023, 1 ), ( 2024, 0 ) } VAR _year_end_offset = MAXX ( FILTER ( _year_end_offset_map, [Value1] = YEAR ( 'Calendar'[Date] ) ), [Value2] ) VAR _week_nr = WEEKNUM ( 'Calendar'[Date], 2 ) + _year_end_offset VAR _week_day = WEEKDAY ( 'Calendar'[Date], 3 ) VAR _day_offset = _week_day + ISODD ( _week_nr ) * 7 RETURN 'Calendar'[Date] - _day_offset
2 Replies
try this code, it assigns the dates correctly for 2022-2025, could probably be derived from nr of days in the first/last week of the year
Payroll Date = VAR _year_end_offset_map = { ( 2022, 1 ), ( 2023, 1 ), ( 2024, 0 ) } VAR _year_end_offset = MAXX ( FILTER ( _year_end_offset_map, [Value1] = YEAR ( 'Calendar'[Date] ) ), [Value2] ) VAR _week_nr = WEEKNUM ( 'Calendar'[Date], 2 ) + _year_end_offset VAR _week_day = WEEKDAY ( 'Calendar'[Date], 3 ) VAR _day_offset = _week_day + ISODD ( _week_nr ) * 7 RETURN 'Calendar'[Date] - _day_offset
