How to find out the first week day date of the month

[Berean_50]

Given a year (E.g. 2021) and month (E.g. October) how do you find out the date of the first Sunday of October, 2021?

I had a look look a the StartOfWeek function in M but it may take me to a previous month if the day of the week starts prior to the first day of the month.

Date	First Sunday of the Month
1 Oct 2021	4 Oct 2021

So in my example, regardless whether the date is 1 Oct 2021 or 31 Oct 2021 it should return 4 Oct 2021 as the the date of the first Sunday in the month of October.

[Syndicate_Admin]

You can still use the M code StartOfWeek function; but start on the 7th of the month instead of the 1st:

   #"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
                Date.AddDays(
                    Date.StartOfMonth([Date]),6)), type date)

[ronrsnfld]

Why are you posting duplicates of my responses?  Not only in this thread, but in others also.

[ronrsnfld · Accepted solution]

You can still use the M code StartOfWeek function; but start on the 7th of the month instead of the 1st:

   #"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
                Date.AddDays(
                    Date.StartOfMonth([Date]),6)), type date)

If you want the first Monday (or other day) of the month, you can use the optional argument for start of week:

#"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
                Date.AddDays(
                    Date.StartOfMonth([Date]),6), Day.Monday))

[LuukTijssen2]

I'd made a caluclated culumn like this in dax:
First Sunday Of The Month =
var firstdayofmonth = STARTOFMONTH([Date])

var daystillnextsunday = 7 - WEEKDAY(firstdayofmonth,2)

return firstdayofmonth + daystillnextsunday

In PowerQuery you can do the same, but as you cannot config weekday like in dax you'd ened a lil adjustment:

AddFirstsundayofmonth =  Table.AddColumn(#"Previousstep", "First Sunday of Month", each Date.AddDays(Date.StartOfMonth([Date]), 6-Date.DayOfWeek(Date.StartOfMonth([Date])) ) , type datetime),