Forum Discussion

BusyBusyBee's avatar
BusyBusyBee
New Member
3 years ago
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...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =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's avatar
wdx223_Daniel
Community Champion
3 years ago

=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's avatar
retep06
New Member
1 year ago

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!