Repeating Roster Cycle

[Mentok]

Hi all - looking for some assistance with what I assume is a fairly simple requirement that I just can't crack >.< 

I have a generated date table in my data, and I need to add in a column denoting the roster cycle. Our rosters work in a 4 week cycle.

So essentially I need:

• Specify a start date (so I can make sure the cycle starts on the correct date for the cycle...)
• Generate numbers in the following sequence: 7 x 1, 7 x 2, 7 x 3, 7 x 4, then repeat from the start.

For example, if we take 2023 so far and assume 01/01/2023 as my start date:

Date	Roster Cycle
1/01/2023	1
2/01/2023	1
3/01/2023	1
4/01/2023	1
5/01/2023	1
6/01/2023	1
7/01/2023	1
8/01/2023	2
9/01/2023	2
10/01/2023	2
11/01/2023	2
12/01/2023	2
13/01/2023	2
14/01/2023	2
15/01/2023	3
16/01/2023	3
17/01/2023	3
18/01/2023	3
19/01/2023	3
20/01/2023	3
21/01/2023	3
22/01/2023	4
23/01/2023	4
24/01/2023	4
25/01/2023	4
26/01/2023	4
27/01/2023	4
28/01/2023	4
29/01/2023	1
30/01/2023	1
31/01/2023	1
1/02/2023	1
2/02/2023	1
3/02/2023	1
4/02/2023	1
5/02/2023	2
6/02/2023	2
7/02/2023	2
8/02/2023	2
9/02/2023	2
10/02/2023	2
11/02/2023	2
12/02/2023	3
13/02/2023	3
14/02/2023	3
15/02/2023	3
16/02/2023	3
17/02/2023	3
18/02/2023	3

My Date table DAX is:

Dates =

VAR BaseCalendar =

    CALENDARAUTO ( 6 )

RETURN

    GENERATE (

        BaseCalendar,

        VAR StartDate = DATE(2022,1,3)

        VAR BaseDate = [Date]

        VAR YearDate = YEAR ( BaseDate )

        VAR MonthNumber = MONTH ( BaseDate )

        VAR Days = ([Date] - StartDate) * 1.

        RETURN ROW (

            "DayDate", BaseDate,

            "Day", DAY( BaseDate ),

            "Year", YearDate,

            "Month Number", MonthNumber,

            "Month", FORMAT (BaseDate, "mmmm" ),

            "Year Month", FORMAT ( BaseDate, "mmm yy" ),

            "Week Num", Weeknum ( BaseDate ),

            "Week Day", WEEKDAY ( BaseDate ),

            "Weekday Name", FORMAT ( BaseDate, "dddd" ),

            "Fortnight", INT(Days/14) + 1

        )

    )

Note: the VAR StartDate, VAR Days, and "Fortnight" are recent dodgy additions by me, which counts the fortnight. This is almost what I'm after except it doesn't repeat, it just continues to increment.

I also have an additional calculated column which marks the active dates that I'm working with from my dataset:

Active Dates =

VAR MaxDate = MAX( Table[Date] )

VAR MinDate = MIN( Table[Date] )

RETURN

SWITCH( TRUE(),

    Dates[Date] < MinDate, BLANK(),

    Dates[Date] > MaxDate, BLANK(),

    Dates[Date]

)  

Specifying a MinDate makes sense for what I'm after with the Roster Cycle, but I wouldn't need a MaxDate, as it just continues in the same cycle...but I don't think it would hurt either?

[Anonymous · Accepted solution]

HI @Mentok,

Here is the calculated table formula to generate a calendar table with date and 7-4 cycle number without any other additional columns.

NewTable = 
VAR _carlendar =
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2030, 12, 31 ) )
RETURN
    ADDCOLUMNS (
        _carlendar,
        "Cycle",
            MOD ( INT ( DATEDIFF ( MINX ( _carlendar, [Date] ), [Date], DAY ) / 7 ), 4 ) + 1
    )

Regards,

Xiaoxin Sheng

[Mentok]

Thank you v-shex-msft! 
I was able to adapt that DAX into my primary date table formula as below:

Dates = 
VAR BaseCalendar = 
    CALENDARAUTO ( 6 ) 
RETURN
    GENERATE (
        BaseCalendar,
        VAR StartDate = DATE(2022,1,3)
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR Days = ([Date] - StartDate) * 1.
        RETURN ROW (
            "DayDate", BaseDate,
            "Day", DAY( BaseDate ),
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT (BaseDate, "mmmm" ),
            "Year Month", FORMAT ( BaseDate, "mmm yy" ),
            "Week Num", Weeknum ( BaseDate ),
            "Week Day", WEEKDAY ( BaseDate ),
            "Weekday Name", FORMAT ( BaseDate, "dddd" ),
            "Fortnight", INT(Days/14) + 1,
            "Cycle", MOD( INT( DATEDIFF( MINX(BaseCalendar, [Date]+2), [Date], DAY) / 7), 4) + 1
        )
    )

I had to add the +2 in the middle, as the VAR "BaseCalendar" was 2 days off the cycle, but easy enough to resolve!

Thanks again.