Change The Day Your Week Starts In Power Query
/By default, Power BI starts its week on Sunday, but that doesn’t work for every situation. Some companies prefer their week start on Monday, and I recently helped someone that needed their week to start on Thursday. Fortunately, by tweaking your date table in Power Query, you can make this change with no calculations at all. Here is what the first few days of 2021 looks like by default:
You can see that Sunday is the start of the week, being 0 for the week day. The formula to return the day of the week is:
The Date functions in Power Query that use day and week calculations have an optional parameter at the end. Simply add the desired day as the second parameter of the Date.DayOfWeek function, so it would be the following to change it to Monday:
Now our Day of Week column looks like this:
To change it to start on Thursday, simply use Day.Thursday and it will adjust accordingly. Day.Sunday, Day.Monday, etc. are just easier ways to tell Power Query when to start. Day.Sunday returns 1, so you could use that in your formula, but that is not as easy to read. Date.DayOfWeek([Date], 3), for example, isn’t immediately obvious that the week is starting on Tuesday.
This is only meaningful in functions that are Week based. Having your first day of the week start on Monday has no bearing on monthly, quarterly, or yearly functions. But functions like Date.DayOfWeek, Date.StartOfWeek, Date.EndOfWeek, and Date.WeekOfMonth can use this first day of week setting.
In the Power Query date table I linked to above, I have a variable called varFirstDayOfWeek that is preset to Day.Sunday. If you change that line, every relevant column in the Date table will adjust to your preferred day of the week.
This will also make your DAX calculations easier as well, since you can just use the week number, day number, start of week date, etc. from the Date table rather than having to remember to add/subtract days to adjust the calendar to your needs.
