Forum Discussion

Mentok's avatar
Mentok
Regular Visitor
3 years ago
Solved

Repeating Roster Cycle

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:

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

 

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's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Mentok
      Regular Visitor
      3 years ago

      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.