cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Berean_50
Frequent Visitor

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

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.

 

DateFirst Sunday of the Month
1 Oct 20214 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.

1 ACCEPTED SOLUTION
ronrsnfld
Resolver IV
Resolver IV

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))

 

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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)

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

ronrsnfld
Resolver IV
Resolver IV

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))

 

View solution in original post

LuukTijssen2
Frequent Visitor

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),

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors