Help with Date.StartOfWeek / End

[LeandroCampacci]

Hi everyone,

I need a little help to solve this: In Power Query, I have a calendar table with the dates of the year. 

Through the expressions Date.StartOfWeek([Data]) and Date.EndOfWeek([Data]) I know the start date and end date of each week (of the year?).

But this brings me a result that I cannot use, since in the week of 09/01/20, I want the beginning of the week of September to be considered 09/01/20 (tuesday) and not 08/30/20 (sunday). The same behavior I want for the end of the week. If the month ends on a Thursday, for example, I want that date to be considered.

Any suggestions?

[v-yingjl · Accepted solution]

Hi @LeandroCampacci ,

You can add an optional parameter in the function Date.StartofWeek() and Date.EndofWeek() to define the start / end day of each week:

= Table.AddColumn(#"Added Custom", "Start", each Date.StartOfWeek([Date],2))

= Table.AddColumn(#"Added Custom1", "End", each Date.EndOfWeek([Date],4))

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[LeandroCampacci]

Oh "sheet", my fault. I used more than 50% of my brain now. 🤣

For the start of the week on currently month:

if [Semana do Mês] = 1
then Date.StartOfMonth([Data])
else Date.StartOfWeek([Data])

Now for the end of the week I don't know if the rule is the same, since the month can end in week 5, 6...

Maybe this?

if [Semana do Mês] = 5 or [Semana do Mês] = 6
then Date.EndOfMonth([Data])
else Date.EndOfWeek([Data])

[vanessafvg]

have you answered your own question at 50%

what you can also do is get the max of the month i think and rather use that to get the week. But I would need sample data to help more.

[vanessafvg]

can you please provide some sample data. When you say you want it to be considered, what demonstrates that its considered, can you show what calculations you are using. What are you expecting as an answer. Its not very clear.

[LeandroCampacci]

Sorry, @vanessafvg. See this GIF: https://imgur.com/0XdSDHb

On first Column show the first day of September Note that the week of the month ("semana do mês") column shows the value "1" for that row, representing the first week of September. But in the next column, beginning of the week ("inicio da semana"), the date is 08/30/20. I need to be 09/01/20.