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 numbers, skipping the weekends. There is a table with the start dates listed, and then a table with sales by date. The two tables are connected, so hoping to reference the start date table and sequentially add days from there.
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))
6 Replies
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))