Future Dates Based On Multiple Conditions

[Anonymous]

Pressed with a deadline, please help.

I am trying to structure a logic but doesn't seem to work out. I have a table that contains discrete as well as duplicate Dates.

What I need is "first-date-of-the-month" in future based on these date values, meeting the following conditions.

1: If the day number of date in CloseDate is less than 15, I want the first of the 5th month.
2: If the day number of date in CloseDate is greater than 15, I want the first of 6th month.

3: if CloseDate is NULL - Last Date of current Month

Side note: I will be using this future date in calculations, do you think "Calucated Column" is the right approach or should I think of Measure?

[Anonymous · Accepted solution]

Hey @Anonymous !

Try this...

Future Date = 
IF(
    DAY(Sheet1[CloseDate]) < 15,
    DATE(YEAR(EDATE(Sheet1[CloseDate],5)), MONTH(EDATE(Sheet1[CloseDate], 5)), 1),
    DATE(YEAR(EDATE(Sheet1[CloseDate],6)), MONTH(EDATE(Sheet1[CloseDate], 6)), 1)
)

Personally, I think the column is fine here as you are not calculating an aggregate, but rather row-by-row.

Hope this helps.

[Anonymous]

Sweet like CTRL+Z ... Worked Perfectly .. Thanks a lot. 

~ I was tying to shoot "DateAdd" bullet.