Forum Discussion
Custom month end date - second to last Friday
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
=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
7 Replies
= let a=Date.EndOfMonth([Date]) in Date.AddDays(a,-List.Select({7..14},each Date.DayOfWeek(Date.AddDays(a,-_))=4){0})
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?
= 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
