Hi,
I have the following line in my date table.
= Table.AddColumn(#“Inserted Week of Month”, “Start of Week”, each Date.StartOfWeek([Date],firstDayofWeek), type date)
I want to change it to recognise Sunday as the first day of the week instead of Monday. Currently 30/8/20 is showing as week 35 and it should be week 36. Ive tried adding -1 in a few places but no joy
Thanks for help in advance
Hi @Hitman,
You need to pass the optional second parameter for the M funciton Date.StartOfWeek, here’s a link to the documentation. Just add Day.Sunday, like so:
= Table.AddColumn(#“Inserted Week of Month”, “Start of Week”, each Date.StartOfWeek([Date], Day.Sunday), type date)
Legend as always Melissa…
Glad I could help 
If that answered your question please don’t forget to mark the thread as Solved, thanks!
For anyone else reading this i found that copying and pasting causes an error as it populates the apostrophe as a 66 and 99 (speech marks) rather than “”.
Melissa clicking tick on solution does make it solved agreed?
Hi Melissa,
Sorry i have noticed another issue with my weeks in my date table and knew it would be easy for you.
My client work on a season basis. I had calculated my measures on week averages and have noticed an issue with my week number over year end
Per the below there are 2 issues
Date_Table.txt (5.0 KB)
Hi @Hitman,
Okay so what qualifies as first week of the year??
Here’s an example for ISO week logic, can you provide something similar? Thanks
Hi Melissa,
Now im really confused 
Per Attached I require the 28th Dec to 3rd of Jan to show as week 53. Then i need to ensure that when sorted weeks 1-4 come after week 53 I’m thinking about creating a mapping table and merging it onto the date table but that does not efficient.
Any ideas
I understand.
But you need to define the “rules” for Week Numbers - the ISO specification lays them down very solidly and is heavily dependent on the week in which the Thursday falls - as a result an “ISO year” can have between 364 and 371 days depending on the first Thursday of the “calendar” week - meaning that Jan 1st can be in Week 52 or 53 of the previous year, or week 1 of “this” year.
So it’s impossible to work out a decent solution without knowing exactly when the year is going to start, you will have to figure that out… a “quick fix” for this year will definitely throw off the week numbering in another years.
Hi @Hitman,
Okay so I think I found something that works but you’ll have to do some checking over multiple years and/or changing start of week days. So without defining custom logic, we’ll rely the standard M function to calculate the Weeknumber.
First Added a new WeekOfYear column with this logic:
Date.WeekOfYear(Date.StartOfWeek([Date],firstDayofWeek),firstDayofWeek)
So the Weeknumber is no longer bound to the [Date] but to the Date.StartOfWeek, this solves the issue where the last days of the week belonging to the next year, getting a different weeknumber.
Next to adjust the Weeknumber when weeks have spilled over into the next year. First Grouped a subset of your Date table by Year, this way we know if there is spillover from the previous year.
To account for that, added a correction value for each date in the year. So if there are spilled over dates AND the year for the Start of Week date < Year then 0 else -1
Finally added this correction value to the new WeekOfYear calculation.
Here’s your updated M code. Date_Table v2.txt (6.0 KB)
I hope this is helpful.
Melissa thank you very much. I am running in and out of meetings this morning but will pay this the attention it deserves either this afternoon or tomorrow morning. Thank you very much for not giving up on me
Hi Melissa. This worked out great thank you. I have also started watching the videos on Power Query and they are great. Congratulations!
