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] ,"" )
Start of Week
I have DAX caluclations that use the STARTOFMONTH function to determine the difference between a date and the start of the month date. But I don't know how to replicate it for the start of the week since STARTOFWEEK is not a function. Here is my logic for start of month: Loans = DISTINCTCOUNT(Opportunity[Id]) Running Total Loans = CALCULATE( [Loans], FILTER( VALUES('Opportunity History By Day'), 'Opportunity History By Day'[Date]<=MAX('Opportunity History By Day'[Date]))) Running Total Start of Month Loans = CALCULATE([Running Total Loans],STARTOFMONTH('Opportunity History By Day Date'[Date])) Running Total Start of Month Loans Variance = [Running Total Loans]-[Running Total Start of Month Loans] Is this possible to achieve for the week?
