Weekly Period Column For Each Month in Power BI

Question

I have a date table with a date column. I would like to have a weekly period for each month. So far, I have built a custom column such that each period starts on Monday and ends on Sunday:

let         
startOfWeek = Date.StartOfWeek([Date]),
endOfWeek = Date.EndOfWeek([Date]),
yearOfStartOfWeek = Date.Year(startOfWeek),
yearOfEndOfWeek = Date.Year(endOfWeek),
    valueToReturn = if yearOfStartOfWeek = yearOfEndOfWeek then
    Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfWeek([Date]))
else
    Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfWeek(endOfWeek))
in valueToReturn

This will give me the custom column "Weekly Period" below:

But what I need to have is this way of weekly period:

• The first week for each month starts with first day of the month and that has to be a working day (not weekend). For example the first week of Sep 2019 starts with Monday 2nd . Also, the last weekly period for each month ends with the last day of month. Do you know how to change this code or which other codes I need to use to have This weekly period like the column below? Many Thanks.

• Also I want another column that gives me the week numbers of the months, as above. I would be grateful if someone can help me build this two custom columns in the date table.

Answer 1

I did it in the query designer (language M) with the following expressions. Go to Add Column > Custom Column (I did it in two separate columns):

'Get first day of month
FirstDayOfMonth = Date.StartOfMonth([Column1]) 'Make sure the outcome is a date format

'Get first working day of month (without Saturday and Sunday)
FirstWorkingDayOfMonth = if Date.DayOfWeek([FirstDayInMonth]) = Day.Saturday 
                            then Date.AddDays([FirstDayInMonth], 2) 
                         else if Date.DayOfWeek([FirstDayInMonth]) = Day.Sunday 
                            then Date.AddDays([FirstDayInMonth], 1) 
                         else [FirstDayInMonth]

For the week number column you can use the Date.DayOfWeek() function, like I used above.

Answer 2

For those that might be helpful, here I found the answer for Weekly Period:

let         
startOfWeek = Date.StartOfWeek([Date]),
endOfWeek = Date.EndOfWeek([Date]),
weekOfStartOfWeek = Date.WeekOfYear(startOfWeek),
weekOfEndOfWeek = Date.WeekOfYear(endOfWeek),

valueToReturn = if  (Date.Month(Date.StartOfWeek([Date])) = Date.Month([Date]) and Date.Month(Date.EndOfWeek([Date])) = Date.Month([Date]))
then 
Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfWeek([Date])) 

else if  Date.Month(Date.StartOfWeek([Date])) <> Date.Month([Date]) then
    Text.From([Date.StartOfMonth([Date])]) & " - " & Text.From(Date.EndOfWeek(endOfWeek))

   else
    Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfMonth([Date]))

in
valueToReturn