topic Re: Create custom date column based on another date in DAX Commands and Tips

Create custom date column based on another date

Vatz8 — Mon, 14 Nov 2022 21:33:43 GMT

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 — Mon, 14 Nov 2022 22:49:01 GMT

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 — Mon, 14 Nov 2022 23:05:26 GMT

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 — Mon, 14 Nov 2022 23:12:18 GMT

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)

)