Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

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