Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SACooper
Helper II
Helper II

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]))
1 ACCEPTED SOLUTION

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:

FreemanZ_0-1701182895478.png

 

View solution in original post

5 REPLIES 5
SACooper
Helper II
Helper II

 

SACooper_0-1701180145573.png

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

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:

FreemanZ_0-1701182895478.png

 

Thank you this is excactly what I needed

 

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

 

FreemanZ_0-1701221821089.png

FreemanZ
Super User
Super User

could you depict your expected result with an table?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors