Forum Discussion
Solved
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 mo...
=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
wdx223_Daniel
Community Champion
Community Champion=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
Awesome! That worked perfectly. Thanks so much.