Add custom date column based on another date.

[Vatz8]

Hi,

I want to create a custom date column with displays 15th of every month and the end of the month based on a date column.

Any day less than or equal to 15th should display 15th and any day less than or equal to the End of the month should display end of month.

For example-->

Given Date           Output Date

10/9/2022             10/15/2022

10/11/2022           10/15/2022

10/17/2022           10/31/2022

10/20/2022           10/31/2022

11/7/2022             11/15/2022

11/17/2022           11/30/2022

[ppm1]

You can use this formula in a custom column in the query editor to get your result.

= if Date.Day([Given Date]) <= 15 then #date(Date.Year([Given Date]), Date.Month([Given Date]), 15) else Date.EndOfMonth([Given Date])

Pat

[AlexisOlson · Accepted solution]

You can add a custom column with this logic:

if Date.Day([Given Date]) <= 15
then Date.AddDays([Given Date], 15-Date.Day([Given Date]))
else Date.EndOfMonth([Given Date])