DATEADD cannot add 14 days

[NatK]

Hi,

I created a custom column to add 14 days to a date (Document Date), but it is returning a blank value. If I change the number of days to 10 or under, it works perfectly. Is there a limitation to the number of days you can use with DATEADD? Or am I missing something?

Result with 14 days:

First Payment Due Date = DATEADD('Purchase Lines'[Document Date], 14, DAY)

Result with 10 days:

First Payment Due Date = DATEADD('Purchase Lines'[Document Date], 10, DAY)

Thanks!

[NatK]

Thank you so much everyone! @v-yalanwu-msft your explanation makes sense. The Document Date column had a max date of 12/2/2022, explaining why I couldn't add more than 10 days. I couldn't open up your PBIX file, but thank you for your help. I ended up going with @djurecicK2 's solution and created another date table that related to Document Date. Then I ran DateAdd on the date field from the new date table. It works perfectly.

Thanks again!

NK

[v-yalanwu-msft · Accepted solution]

Hi, @NatK ;

Because the date returned by dateadd is first the date in 'Purchase Lines' [Document Date], if the resulting date is not in this 'Purchase Lines' [Document Date] column, it returns empty. For example, 2022-11-22 +14day=2022-12-6; However, 2022-12-6 is not in your date column ... however 2022-11-22 +10day=2022-12-2 in your column, so it return value.

we could modify measure as follow:

Dateadd = 
DATEADD('Table'[Date],10,DAY)

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djurecicK2]

Hi @NatK ,

 You many want to consider adding a separate date table to your model.

https://blog.enterprisedna.co/tips-in-creating-power-bi-date-tables/

[Seanan]

Hi @NatK,

Could you please try creating it as a measure instead of a calculated column. I've tried this in my test and it worked fine for me.