Forum Discussion

SACooper's avatar
SACooper
Helper II
2 years ago
Solved

Custom Cycle within Date Calendar

I am trying to add a custom Production Cycle Start Date to a date table. There is a production cycle lasting 14 days starting on a Thursday (the latest starting 23/11/2023) - i'd like to use this dim...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi SACooper ,

     

    not sure if i fully get you, try to create a calculated table like:

    dates = 
VAR _table = CALENDAR(DATE(2023,11,23), DATE(2024,1,1))
RETURN
ADDCOLUMNS(
    _table,
    "PeriodStart",
    VAR _gap =  MOD( DATEDIFF([date], DATE(2023,11,23), DAY), 14)
    VAR _startdate =
        MAXX(
            FILTER(
                _table,
                [date]<=EARLIER([date])
                    && MOD( DATEDIFF([date], DATE(2023,11,23), DAY), 14) =0
            ),
            [date]
        )
    RETURN
    IF(
        _gap=0,
        [date],
        _startdate
    )
)

     

    it worked like:

     

SACooper's avatar
SACooper
Helper II
2 years ago

 

here's Mock up of what I would be expecting the cucstom cycle in red.

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    hi SACooper ,

     

    not sure if i fully get you, try to create a calculated table like:

    dates = 
VAR _table = CALENDAR(DATE(2023,11,23), DATE(2024,1,1))
RETURN
ADDCOLUMNS(
    _table,
    "PeriodStart",
    VAR _gap =  MOD( DATEDIFF([date], DATE(2023,11,23), DAY), 14)
    VAR _startdate =
        MAXX(
            FILTER(
                _table,
                [date]<=EARLIER([date])
                    && MOD( DATEDIFF([date], DATE(2023,11,23), DAY), 14) =0
            ),
            [date]
        )
    RETURN
    IF(
        _gap=0,
        [date],
        _startdate
    )
)

     

    it worked like:

     

    • SACooper's avatar
      SACooper
      Helper II
      2 years ago

      Thank you this is excactly what I needed

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        2 years ago

        hi SACooper ,

         

        you may also try this:

        dates2 = 
VAR _table = CALENDAR(DATE(2023,11,23), DATE(2024,1,1))
RETURN
    ADDCOLUMNS(
        _table,
        "column",
        VAR _date = [date]
        RETURN
        MAXX(
            FILTER(
                _table, 
                AND(
                    [date]<=_date,
                    MOD( DATEDIFF([date], DATE(2023,11,23), DAY), 14)=0
                )
            ),
            [date]
        )
    )