Create Week Start date column within a date table

[powerlight1]

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]

    ,""

)

[powerlight1]

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 · Accepted solution]

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)

[powerlight1]

@MasonMA 

love the solution, so simple