Offsetting Week Start Day

Scenario: You have a date dimension table and typical date-related columns, such as DayOfWeek, WeekEndDate, etc. In US, weeks start on Sunday and end on Saturday (ISO weeks on the other hand start on Monday and end on Sunday). You have a requirement to overwrite the week start and end days for a Power Pivot, Tabular, or MD model. For example, while the Date table defines the week start day on Sunday, you might need to overwrite it to start on Monday.

Solution: I typically derive as many of the date calculations in a SQL view on top of the Date table instead of saving the results in the table itself. For example, the WeekOfYear calculation might look like this:

DATEPART(WEEK, [Date]) [WeekOfYear]

If you have date calculations like this, instead of changing all week-related calculations to reflect the desired week start day, the easiest way to offset the week day is to use the T-SQL SET DATEFIRST statement preceding the actual SELECT statement. The following example taken from Power Pivot/Tabular overwrites the first day of the week to start on Monday.