Forum Discussion
Sequential Dates
Hello. I am trying to assign numbers to dates, but not based on the 365 calendar. I need it to start on a certain date (9/18/2022 for example) as day 1, and sequentially count the days as number...
Anonymous Don't filter out the weekends then (the < 6 part):
Sequential Date Number = VAR __StartDate = MAX('Table'[StartDate]) VAR __Date = MAX('Table2'[Date]) VAR __Table = CALENDAR(__StartDate, __Date), RETURN COUNTROWS(FILTER(__Table))
Anonymous Sounds similar to Sequential. Sequential - Microsoft Power BI Community
Maybe something like:
Sequential Date Number =
VAR __StartDate = MAX('Table'[StartDate])
VAR __Date = MAX('Table2'[Date])
VAR __Table =
ADDCOLUMNS(
CALENDAR(__StartDate, __Date),
"__WeekDay",WEEKDAY([Date],2)
)
RETURN
COUNTROWS(FILTER(__Table,[__WeekDay]<6))Mainly guessing, no sample data, etc.
Hi Greg_Deckler -
Here is an example of what I am trying to do:
The "Early Buy Start Date" is the refernce date I need as the start date for the sequence (9/19/2022 in this example). This is in a different table in Power BI. The "Day" column is the field I need to calculate.
Anonymous Don't filter out the weekends then (the < 6 part):
Sequential Date Number = VAR __StartDate = MAX('Table'[StartDate]) VAR __Date = MAX('Table2'[Date]) VAR __Table = CALENDAR(__StartDate, __Date), RETURN COUNTROWS(FILTER(__Table))Greg_Deckler - When I tried that, I got 9 as the return value for every date. Did I not change something properly?:
Sequential Date Number =VAR __StartDate = MAX('Early Buy Start Date'[EarlyBuyStartDate])VAR __Date = MAX('Pool Early Buy'[Order Date])VAR __Table =ADDCOLUMNS(CALENDAR(__StartDate, __Date),"__WeekDay",WEEKDAY([Date],2))RETURNCOUNTROWS(FILTER(__Table,[__WeekDay]<6))Anonymous I was thinking measure and you probably are creating a calculated column.
Sequential Date Number = VAR __StartDate = MAX('Early Buy Start Date'[EarlyBuyStartDate]) VAR __Date = 'Pool Early Buy'[Order Date] VAR __Table = ADDCOLUMNS( CALENDAR(__StartDate, __Date), "__WeekDay",WEEKDAY([Date],2) ) RETURN COUNTROWS(FILTER(__Table,[__WeekDay]<6))