Forum Discussion

powerlight1's avatar
powerlight1
Helper I
8 months ago
Solved

Create Week Start date column within a date table

Hi community,

 

I have a table with a date column and I am trying to create a new column that is Week_Starting (Monday).  I have already created the Week Num and Day of Week columns and I wanted to create a new Week Starting column where DayOfWk =1 within each of the Week_num rows. For example for all rows where Week_Num = 32, I want to select the row where DayOfWk=1 (ie 6/08/2024) and populate the new Week_Startig column with 6/08/2024.  I don't want to create another summarized table with only Week_Num and filter on where DayOfWk is only 1 and link this summarised table back into this table because my data model already has too many tables.  Is it possible to create this Week_Start column within this date table?

 

 

I wrote the below code but could not get it to work

 

 

WeekStart =
VAR _weeknum = 'Period Filter'[Week_Num]
VAR _table1 =
            SUMMARIZECOLUMNS(
                            'Period Filter'[Date],'Period Filter'[Week_Num]
                            ,FILTER('Period Filter','Period Filter'[DayOfWk]=1)
            )
RETURN
IF(
    _table1[Week_Num] = _weeknum
    ,_table1[Date]
    ,""
)

 

"DAX "
Startofweek
  • MasonMA's avatar
    MasonMA
    8 months ago

    Hi,

     

    If your DATE_LYSLS is continuous and there is no missing date, try this for a new column

    WeekStart = [DATE_LYSLS] - (WEEKDAY([DATE_LYSLS], 2) - 1)

     

3 Replies

  • powerlight1's avatar
    powerlight1
    Helper I
    8 months ago

    hi community,

     

    I tried rewriting the DAX statement by first creating a summarised table (_table1) and then use the summarised table to link back to the 'Period Filter' table using the Week_Num columns and return the Date column in the _table1.  However you can see I got an error message.

     

     

    • MasonMA's avatar
      MasonMA
      Super User
      8 months ago

      Hi,

       

      If your DATE_LYSLS is continuous and there is no missing date, try this for a new column

      WeekStart = [DATE_LYSLS] - (WEEKDAY([DATE_LYSLS], 2) - 1)