Forum Discussion
Custom month end date - second to last Friday
=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
Community Champion= 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?
- 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
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
