Sequential Dates

[Anonymous]

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. 

[Greg_Deckler]

@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.

[Anonymous]

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. 

[Greg_Deckler · Accepted solution]

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

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

)

RETURN

COUNTROWS(FILTER(__Table,[__WeekDay]<6))

[Greg_Deckler]

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

[Anonymous]

@Greg_Deckler - The measure does not work due to the fact that there are multiple order dates. I get an error saying that a single value can not be determined. Even when I try to add a column, I get this error: "The start date in Calendar function can not be later than the end date."