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