Power Query - First Monday of the current month

This is fantastic, thank you!

Can you advise how I could adjust the M code to show the date of the Sunday before the first Monday of the next month? e.g. for 4th April 2024, it would be 5th May 2024.

Hi again VicCC 

Sure thing! Here are a couple of options (sample PBIX attached):

1. Add one month to the Date and apply the same logic as "First Monday of the Month", then subtract one day:

let
  Dates = Table.FromColumns(
    {List.Dates(#date(2024, 1, 1), 366, #duration(1, 0, 0, 0))},
    type table [Date = date]
  ),
  #"Add Day Name" = Table.AddColumn(Dates, "Day Name", each Date.DayOfWeekName([Date]), type text),
  #"Added First Monday of Month" = Table.AddColumn(
    #"Add Day Name",
    "First Monday of Month",
    each
      let
        SOM         = Date.StartOfMonth([Date]),
        SOM_Weekday = Date.DayOfWeek(SOM, Day.Monday),
        DaysToAdd   = Number.Mod(7 - SOM_Weekday, 7)
      in
        Date.AddDays(SOM, DaysToAdd),
    type date
  ),
  #"Added Sunday Before First Monday of Next Month" = Table.AddColumn(
    #"Added First Monday of Month",
    "Sunday Before First Monday of Next Month",
    each
      let
        SOM         = Date.StartOfMonth(Date.AddMonths([Date],1)),
        SOM_Weekday = Date.DayOfWeek(SOM, Day.Monday),
        DaysToAdd   = Number.Mod(7 - SOM_Weekday, 7) - 1
      in
        Date.AddDays(SOM, DaysToAdd),
    type date
  )
in
  #"Added Sunday Before First Monday of Next Month"

2. Create a function fn_FirstMondayOfMonth and use this for both columns:

// fn_FirstMondayOfMonth
( Date as date ) as date =>
let
    SOM         = Date.StartOfMonth(Date),
    SOM_Weekday = Date.DayOfWeek(SOM, Day.Monday),
    DaysToAdd   = Number.Mod(7 - SOM_Weekday, 7)
in
    Date.AddDays(SOM, DaysToAdd)

// Date_using_function
let
  Dates = Table.FromColumns(
    {List.Dates(#date(2024, 1, 1), 366, #duration(1, 0, 0, 0))},
    type table [Date = date]
  ),
  #"Add Day Name" = Table.AddColumn(Dates, "Day Name", each Date.DayOfWeekName([Date]), type text),
  #"Added First Monday of Month" = Table.AddColumn(
    #"Add Day Name",
    "First Monday of Month",
    each fn_FirstMondayOfMonth([Date]),      
    type date
  ),
  #"Added Sunday Before First Monday of Next Month" = Table.AddColumn(
    #"Added First Monday of Month",
    "Sunday Before First Monday of Next Month",
    each Date.AddDays(fn_FirstMondayOfMonth(Date.AddMonths([Date],1)),-1),
    type date
  )
in
  #"Added Sunday Before First Monday of Next Month"

 Regards