Custom Cycle within Date Calendar

SACooper — Tue, 28 Nov 2023 13:32:50 GMT

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]))

Re: Custom Cycle within Date Calendar

FreemanZ — Tue, 28 Nov 2023 13:45:38 GMT

could you depict your expected result with an table?

Re: Custom Cycle within Date Calendar

SACooper — Tue, 28 Nov 2023 14:03:03 GMT

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

Re: Custom Cycle within Date Calendar

FreemanZ — Tue, 28 Nov 2023 14:48:58 GMT

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:

Re: Custom Cycle within Date Calendar

SACooper — Tue, 28 Nov 2023 16:54:53 GMT

Thank you this is excactly what I needed

Re: Custom Cycle within Date Calendar

FreemanZ — Wed, 29 Nov 2023 01:37:13 GMT

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] ) )

topic Re: Custom Cycle within Date Calendar in DAX Commands and Tips

Custom Cycle within Date Calendar

Re: Custom Cycle within Date Calendar

Re: Custom Cycle within Date Calendar

Re: Custom Cycle within Date Calendar

Re: Custom Cycle within Date Calendar

Re: Custom Cycle within Date Calendar