Last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month in Power Query Language M

[Vijay_A_Verma]

Use Case - I blogged about calculating First Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month in Power Query Language M.
Here, I am going to talk about the last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month. There are many business scenarios where you would be asked to find Last Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday of the Month. For example, monthly Townhall meeting should be held on last Friday of the month. Hence, if a date is given, following should be the answer

Solution - Following formulas can be used to calculate these dates

Last Monday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),1)

Last Tuesday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),2)

Last Wednesday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),3)

Last Thursday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),4)

Last Friday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),5)

Last Saturday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),6)

Last Sunday of the Month
= Date.StartOfWeek(Date.EndOfMonth([Date]),0)

Note - 1, 2....0 can also be replaced with Day.Monday, Day.Tuesday.......Day.Sunday. Hence, for Monday it can be written as

= Date.StartOfWeek(Date.EndOfMonth([Date]),Day.Monday)

But this is possible only if you follow English language. Hence, it is better to use 1,2.....0 not Day.Monday, Day.Tuesday.....Day.Sunday

Following Query M code can be used to test above

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9U3MjAyVIrVAfL0gXwgzwjMM9I3NEPwjPUtEBwTfSR1QAOQ1JlBDARyYgE=", 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 date}}),
    LastMonday = Table.AddColumn(#"Changed Type", "Mon", each Date.StartOfWeek(Date.EndOfMonth([Date]),1), type date),
    LastTuesday = Table.AddColumn(LastMonday, "Tue", each Date.StartOfWeek(Date.EndOfMonth([Date]),2), type date),
    LastWednesday = Table.AddColumn(LastTuesday, "Wed", each Date.StartOfWeek(Date.EndOfMonth([Date]),3), type date),
    LastThursday = Table.AddColumn(LastWednesday, "Thu", each Date.StartOfWeek(Date.EndOfMonth([Date]),4), type date),
    LastFriday = Table.AddColumn(LastThursday, "Fri", each Date.StartOfWeek(Date.EndOfMonth([Date]),5), type date),
    LastSaturday = Table.AddColumn(LastFriday, "Sat", each Date.StartOfWeek(Date.EndOfMonth([Date]),6), type date),
    LastSunday = Table.AddColumn(LastSaturday, "Sun", each Date.StartOfWeek(Date.EndOfMonth([Date]),0), type date)
in
    LastSunday

--- End of Article ---