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

[retep06]

Hi! Sorry will be asking in this thread. Need you help similiar to this.
I want to add a custom column based on my [Date] column.

The custom column should be Last Friday of the Month, or if the [Date falls] after Last Friday of the Month, it will fall under Last Friday of Next Month.

Sample:

26-Nov-2024 will fall under 29-Nov-2024

30-Nov-2024 will fall under 27-Dec-2024

30-Dec-2024 will fall under 31-Jan-2025

Thank you in advance!

[BusyBusyBee]

Awesome! That worked perfectly. Thanks so much.