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

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