Forum Discussion
Future Dates Based On Multiple Conditions
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?
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.
2 Replies
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.