cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Top Kudoed Posts
Latest Articles
Archives