Power Query - First Monday of the current month

[VicCC]

Hi, I'm looking to create a custom column which reflects the first Monday of the current month. So if the date in the [date] column is May 2nd 2024, it would return May 6th 2024. Can this be calculated in Power Query? I also wish to find out the date of the Sunday before this Monday in order to calculate the end of a period. Thanks in advance

[VicCC · Accepted solution]

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.

[VicCC · Accepted solution]

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.

[OwenAuger · Accepted solution]

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

[VicCC]

Wonderful, thank you so much for demonstrating the amendment required!

[OwenAuger]

Hi @VicCC 

I would suggest something like the example below.

The pseudocode for the First Monday of Month is:

[First Monday of Month] =
  [First Day of Month] + MOD ( 7 - WEEKDAY ( [First Day of Month] ), 7 )
// WEEKDAY returns 0 for Mon & 6 for Sun

let
  Dates = Table.FromColumns(
    {List.Dates(#date(2024, 1, 1), 366, #duration(1, 0, 0, 0))},
    type table [Date = date]
  ),
  // Add Day Name for reference only
  #"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 Month" = Table.AddColumn(
    #"Added First Monday of Month",
    "Sunday Before First Monday of Month",
    each Date.AddDays([First Monday of Month], - 1),
    type date
  )
in
  #"Added Sunday Before First Monday of Month"

Does something like this work for you?