Custom month end date - second to last Friday

[BusyBusyBee]

Hi there,

I'm wanting to create a custom column in Power Query based on the Date column to calculate the month end date being the second to last Friday of the month.

eg Date 13/03/2023 and month end date would be 24/03/2023

eg Date 07/02/2023 and month end date would be 17/02/2023

Any help would be greatly appreciated

[wdx223_Daniel]

= let a=Date.EndOfMonth([Date]) in Date.AddDays(a,-List.Select({7..14},each Date.DayOfWeek(Date.AddDays(a,-_))=4){0})

[BusyBusyBee]

Wow, that's great @wdx223_Daniel. I do have a slight problem with this where I should have specified in my note above. For any date that falls after the month end date it would then be allocated to the next month end date.

eg Date 13/03/2023 and month end date would be 24/03/2023

eg Date 25/03/2023 and month end date would be 21/04/2023

eg Date 17/04/2023 and month end date would be 21/04/2023

eg Date 28/04/2023 and month end date would be 19/05/2023

Would you have a suggestion on an updated formula?

[wdx223_Daniel]

= let fx=(dt)=>Date.AddDays(dt,-List.Select({7..14},each Date.DayOfWeek(Date.AddDays(dt,-_))=4){0}),a=Date.EndOfMonth([Date]),b=fx(a) in if [Date]>b then fx(Date.AddMonths(a,1)) else b

[BusyBusyBee]

Thanks again @wdx223_Daniel 
There is a slight problem coming through with particular dates where it is being calculated incorrectly.

For a date between 18/02/2023 - 28/02/2023 the month end date is being calculated as 17/03/2023 instead of the 24/03/2023

For 2024, the dates between 20/04/2024 to 30/04/2024 the month end date is being calculated as the 17/05/2024 instead of 24/05/2024

It seems to be one section each year where it's calculating the month end date incorrectly but all other dates calculate fine.

Appreciate the help and time you have put into my issue

[wdx223_Daniel · Accepted solution]

=let fx=(dt)=>Date.AddDays(dt,-List.Select({7..14},each Date.DayOfWeek(Date.AddDays(dt,-_))=4){0}),a=Date.EndOfMonth([Date]),b=fx(a) in if [Date]>b then fx(Date.EndOfMonth(Date.AddMonths(a,1))) else b

[BusyBusyBee]

Awesome! That worked perfectly. Thanks so much.