First Mon, Tue, Wed, Thu, Fri, Sat and Sun of the …

Use Case – There are many business scenarios where you would be asked to find first Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday of the Month. For example, monthly steering committee meeting will be held on first Thursday of the month every month. Hence, if a date is given, following should be the answer


Solution – Following formulas can be used to calculate these dates


First Monday of the Month
= Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),1)

First Tuesdday of the Month

First Wednesday of the Month

First Thursday of the Month

First Friday of the Month

First Saturday of the Month

First Sunday of the Month


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.AddDays(Date.StartOfMonth([Date]),6),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


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9U3MjAyVIrVAfL0gXwgzwjMAzJNETxjfUMEx0TfyALBM9U3Q3DMUMww1zeCaYsFAA==", 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}}),
    FirstMonday = Table.AddColumn(#"Changed Type", "Monday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),1), type date),
    FirstTuesday = Table.AddColumn(FirstMonday, "Tuesday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),2), type date),
    FirstWednesday = Table.AddColumn(FirstTuesday, "Wednesday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),3), type date),
    FirstThursday = Table.AddColumn(FirstWednesday, "Thursday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),4), type date),
    FirstFriday = Table.AddColumn(FirstThursday, "Friday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),5), type date),
    FirstSaturday = Table.AddColumn(FirstFriday, "Saturday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),6), type date),
    FirstSunday = Table.AddColumn(FirstSaturday, "Sunday", each Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Date]),6),0), type date)


— End of Article —

Source link

Be the first to comment

Leave a Reply

Your email address will not be published.