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.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors