creating a calculated column in the Date table

[dexter]

I have the below Date table, i want to add a new column which shows startOftheWeek date(in my case start of the week should be every thursday).
How can i calculate the start of the week(every thursday) and show that in the newly created column in the Date table below.

Date =
ADDCOLUMNS (
CALENDARAUTO ();
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

[Chihiro · Accepted solution]

Something like below. Apply formatting as required.

Date =
ADDCOLUMNS (
CALENDARAUTO ();
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1
)

[dexter]

What does this line do, can you please elloborate. Why are you substracting WEEKDAY([Date],14)+1?

"startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1

[Chihiro]

WEEKDAY formula with argument of 14 for pattern returns WEEKDAY number 1 for Thursday, ending with 7 for Wednesday.

So date minus weekday number will give last Wednesday’s date. Add back 1 day and you get week start day of Thursday.

[dexter]

Sorry, i could not understand why are you using 14. As in this link https://msdn.microsoft.com/en-us/query-bi/dax/weekday-function-dax, they specified WEEKDAY(<date>, <return_type>) and return_type is either 1 or 2 or 3.

[Chihiro]

That documentation isn't complete.

Argument can use following values.

1 to 3 and 11 to 17.

11 to 17 is useful when you want week to start on specific weekday.

11 = Monday = 1

12 = Tuesday = 1

...

14 = Thursday = 1

...

You get the idea.

[dexter]

@Chihiro, Can i create a new measure which shows thursday's date as the weekstart date in my existing productTable which has ID,prodName,status,DeliveredDate,Comments as the columns. The before accepted solution is great but we are creating a separate Date table instead of it i just want to create one calculated column or measure in my existing table if possible?