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
Icon for Community Champion rankCommunity 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.AddMonths(a,1)) else b

BusyBusyBee's avatar
BusyBusyBee
New Member
3 years ago

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's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity 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

    • BusyBusyBee's avatar
      BusyBusyBee
      New Member
      3 years ago

      Awesome! That worked perfectly. Thanks so much.

    • 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!