Bi-Weekly Calendar

[rsbin]

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,

[Stachu · Accepted solution]

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

[rsbin]

@Stachu ,

Awesome!!
Now to study it in more detail to figure out how it works.
Thank you so much for the solution!!!

Kindest Regards,