Calculated Column - Dateadd Formula - Blank Results

[roet]

Good afternoon,

I created a calculated column in a table with this formula

DATEADD(Customers[DateFirstPurchase],90,Day)

I am trying to add 90 days to the original date.

The formula brings up correct calculations except there are some source dates that are not calculating a target date (blank cell, no error message).

The source excel file and the data model have the source column set to a date format correctly.

I experimented and changed the year of the date (from 5/7/2004 to 5/7/2001) of one of the cells not calculating in the source excel file and the calculation worked as expected. I went back to the source excel and returned the original entry and the calculated value went blank again. 

I am not sure how this might affect the results but the date range is from 7/1/2001 - 5/7/2004

the dates from 7/1/2001 - 3/30/2004 are calculating correctly

The dates from 5/3/2004-7/31/2004 are not calculating (blank)

It seems like the results are limited to a certain date maximum but I did not intend for that to happen.

Thanks for your thoughts on this.

[Sahir_Maharaj]

Hello @roet,

Can you please try the following:

NewDate = Customers[DateFirstPurchase] + 90

IF(ISBLANK(Customers[DateFirstPurchase]), "Blank Source Date", DATEADD(Customers[DateFirstPurchase], 90, DAY))

[Anonymous]

Hi @roet ,

After many days, I am still very concerned about your problem.

Has your problem been solved yet?

If you have any other questions, please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[roet]

Thank you for coming back to me on this. Unfortunately, the same issue came up with the proposed solution.

I have moved on from this issue.

Thank you again.

[Anonymous]

Hi @roet ,

Dateadd: Returns a table containing a column of dates.

Create the date table and then associate the date table with the fact table. dateadd() function requires consecutive date's.

Or you can try EDATE or EOMONTH.

Column 1 = EDATE(Customers[DateFirstPurchase],3)

Column 2 = EOMONTH(Customers[DateFirstPurchase],3)

For more information about these two functions see:

EDATE function (DAX) - DAX | Microsoft Learn

EOMONTH function (DAX) - DAX | Microsoft Learn

If you still have questions after trying it, please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!