The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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]))
Solved! Go to 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:
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:
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]
)
)
could you depict your expected result with an table?
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
31 | |
11 | |
10 | |
10 | |
9 |