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

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.