Create 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

[FreemanZ]

Supposing your table named Data, try to add a new column with the code below:

Output Date =

IF( 

    DAY(Data[Given Date])<=15, 

    DATE(YEAE(Data[Given Date]), MONTH(Data[Given Date]),15),

    ENDOFMONTH(Data[Given Date])

)

[Vatz8]

Hi by using this formula I get all the dates with 15. But the endofmonth returns the same date as in the given column.

[FreemanZ · Accepted solution]

please try this 

Output Date =

IF( 

    DAY(Data[Given Date])<=15, 

    DATE(YEAE(Data[Given Date]), MONTH(Data[Given Date]),15),

    EOMONTH(Data[Given Date],0)

)