How do I calculate last business date in a month in M Query?

[phontalba]

Hi, I want to add a custom conditional column that calculates the last business date of the month. I would like to use Date.EndofMonth and specify that it needs to be a week day.

[Anonymous]

Hi @phontalba ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new End of month column in power query editor.

3.Create the new weekday column.

4.Create the new column to calculate last business date in a month.

if [Weekday] = 5 then Date.AddDays([EndOfMonth], -1) 
        else if [Weekday] = 6 then Date.AddDays([EndOfMonth], -2) 
        else [EndOfMonth]

5.You can also use the Advanced editor.

let
    // Replace with your date column#date(2024, 8, 27)
    Source = Table.FromRecords({[Date = #date(2024, 8, 27)]}),
    AddEndOfMonth = Table.AddColumn(Source, "EndOfMonth", each Date.EndOfMonth([Date])),
    AddWeekday = Table.AddColumn(AddEndOfMonth, "Weekday", each Date.DayOfWeek([EndOfMonth], Day.Monday)),
    LastBusinessDay = Table.AddColumn(AddWeekday, "LastBusinessDay", each 
        if [Weekday] = 5 then Date.AddDays([EndOfMonth], -1) 
        else if [Weekday] = 6 then Date.AddDays([EndOfMonth], -2) 
        else [EndOfMonth]
    ),
    RemoveColumns = Table.RemoveColumns(LastBusinessDay,{"EndOfMonth", "Weekday"})
in
    RemoveColumns

// let
//     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddGxDYAwEATBVizHSP47GzBkFEAFlvtvA8hZ6bONbn6M3IuKwy3JZ8R76brzXL5gCpVCo7BS2CjsFDqFg4ICC24XjheuF84X7hcCCAWEBEIDo4H5/2hgNDAaGA2MBkYDo4HRoKJB/TOYDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
//     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}})
// in
//     #"Changed Type"

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NHarington · Accepted solution]

If you need to do this in an M query you can use the following code as a custom column

let
EndOfMonthDate = Date.EndOfMonth([YourDateColumn]),
DayOfWeek = Date.DayOfWeek(EndOfMonthDate, Day.Sunday),
LastBusinessDay = if DayOfWeek = 0 then Date.AddDays(EndOfMonthDate, -2)
else if DayOfWeek = 6 then Date.AddDays(EndOfMonthDate, -1)
else EndOfMonthDate
in
LastBusinessDay

Make sure to replace [YourDateColumn] with your date column

[NHarington]

Hi, You can use the below dax for a new column.

LastBusinessDayOfMonth =
VAR EndOfMonthDate = EOMONTH([YourDateColumn], 0)
VAR DayOfWeek = WEEKDAY(EndOfMonthDate, 2)
RETURN
IF(DayOfWeek = 6, EndOfMonthDate - 1,
IF(DayOfWeek = 7, EndOfMonthDate - 2,
EndOfMonthDate
)
)

Make sure to replace [YourDateColumn] with your date column