Forum Discussion

Vatz8's avatar
Vatz8
Helper I
3 years ago
Solved

Add custom date column based on another date.

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

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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])

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    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])

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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