How to get the last Friday of each month?

[VQH]

I know you can use Date.EndOfMonth to get the last day of the month, but I need to grab the last Friday of each month, in other words the last working day of each month. Any help would be appreciated. 

[wdx223_Daniel]

let
    Source = #date(2022,8,8),
    LastFriday=List.Last(List.Generate(()=>Date.EndOfMonth(Source),each Date.DayOfWeek(_)<>3,each Date.AddDays(_,-1)))
in
    LastFriday

[KT_Bsmart2gethe]

Hi @VQH ,

See below code how to get a list of date that is Friday:

let
    StartDate   =   #date(2022,1,1),
    EndDate     =   #date(2022,12,31),
    Calendar = List.Transform(
                    List.Select(
                        { Number.From(StartDate)..Number.From(EndDate) }, 
                        each 
                            Date.DayOfWeekName(_)="Friday"
                    ), 
                    each 
                        Date.From(_)
                )
in
    Calendar

Regards

KT

[VQH]

I can get the friday of each week no problem, how do I get the last Friday of each month? Example the last Friday of July 2022 is July 29 2022

[jbwtp · Accepted solution]

Hi @VQH,

you just need to add an additional condition. Something in lines with:

Date.DayOfWeekName(_)="Friday" and Number.From(Date.EndOfMonth(DateTime.From(_))) - _ < 7

This also checks that this is the last week week in the month (less than 7 days before the EOM). 

Kind regards,

John

[jbwtp]

Hi all,

also an interesting solution inspired by this post :

let
    StartDate   =   202201,
    EndDate     =   202212,
    Calendar = List.Transform({StartDate..EndDate}, each Date.StartOfWeek(Date.EndOfMonth(Date.FromText(Text.From(_*100+1))), Day.Friday))
in
    Calendar

Kind regards,

John

[VQH]

Thanks, this helped me figure it out. I just need to pass in this: 

= each Date.StartOfWeek(Date.EndOfMonth([ColumnName]), Day.Friday)