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 dimension for some reporting,

The table is defined as:

Date = 

VAR _fromYear=YEAR(MIN('Members'[Date created])) // set the start year of the date dimension. dates start from 1st of January of this year
VAR _toYear=YEAR(MAX('Members'[Date created])) + 1   // set the end year of the date dimension. dates end at 31st of December of this year

//************** 
VAR _today=TODAY()
RETURN
ADDCOLUMNS(
    CALENDAR(
                DATE(_fromYear,1,1),
                DATE(_toYear,12,31)
),
"Year",YEAR([Date]),
"Start of Year",DATE( YEAR([Date]),1,1),
"End of Year",DATE( YEAR([Date]),12,31),
"Month",MONTH([Date]),
"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),
"End of Month",EOMONTH([Date],0),
"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,
"Year Month Number",INT(FORMAT([Date],"YYYYMM")),
"Year Month Name",FORMAT([Date],"YYYY-MMM"),
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Name Short",FORMAT([Date],"DDD"),
"Day of Week",WEEKDAY([Date]),
"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,
"Month Name",FORMAT([Date],"MMMM"),
"Month Name Short",FORMAT([Date],"MMM"),
"Week of Year",WEEKNUM([Date],2),
"Start of Week", [Date]-WEEKDAY([Date],2)+1,
"End of Week",[Date]+7-WEEKDAY([Date], 2),
"Day Offset",DATEDIFF(_today,[Date],DAY),
"Month Offset",DATEDIFF(_today,[Date],MONTH),
"Year Offset",DATEDIFF(_today,[Date],YEAR)
)

.

I have had a few unsuccessful attempts at building this production cycle into the date table, the latest being the below, but this isn't repeating the values for each day within a cycle and looks to be just wrong.

Date = 

VAR _fromYear=YEAR(MIN('Members'[Date created])) // set the start year of the date dimension. dates start from 1st of January of this year
VAR _toYear=YEAR(MAX('Members'[Date created])) + 1   // set the end year of the date dimension. dates end at 31st of December of this year

VAR Prod_Base_Start_Date = 
    DATE(1900, 1, 1) -  
        INT( 
            DATEDIFF(
                DATE(2023, 11, 23),
                DATE(1900, 1, 1), 
                DAY
            ) / 14
        ) * 14
 

//************** 
VAR _today=TODAY()
VAR DateTable =
ADDCOLUMNS(
    CALENDAR(
                DATE(_fromYear,1,1),
                DATE(_toYear,12,31)
),
"Year",YEAR([Date]),
"Start of Year",DATE( YEAR([Date]),1,1),
"End of Year",DATE( YEAR([Date]),12,31),
"Month",MONTH([Date]),
"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),
"End of Month",EOMONTH([Date],0),
"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,
"Year Month Number",INT(FORMAT([Date],"YYYYMM")),
"Year Month Name",FORMAT([Date],"YYYY-MMM"),
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Name Short",FORMAT([Date],"DDD"),
"Day of Week",WEEKDAY([Date]),
"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,
"Month Name",FORMAT([Date],"MMMM"),
"Month Name Short",FORMAT([Date],"MMM"),
"Week of Year",WEEKNUM([Date],2),
"Start of Week", [Date]-WEEKDAY([Date],2)+1,
"End of Week",[Date]+7-WEEKDAY([Date], 2),
"Day Offset",DATEDIFF(_today,[Date],DAY),
"Month Offset",DATEDIFF(_today,[Date],MONTH),
"Year Offset",DATEDIFF(_today,[Date],YEAR),
"Days_Since_Prod_Start", DATEDIFF([Date], Prod_Base_Start_Date, DAY)
)

RETURN
    ADDCOLUMNS(
        DateTable,
        "Prod_Cycle_Start_Date", IF( MOD([Days_Since_Prod_Start], 14) = 0, Prod_Base_Start_Date + [Days_Since_Prod_Start]))
  • 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:

     

5 Replies

  •  

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

    • FreemanZ's avatar
      FreemanZ
      Super User

      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: