Forum Discussion
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
- FreemanZSuper User
could you depict your expected result with an table?
- SACooperHelper II
here's Mock up of what I would be expecting the cucstom cycle in red.
- FreemanZSuper 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:
- SACooperHelper II
Thank you this is excactly what I needed