Forum Discussion

NatK's avatar
NatK
Helper I
3 years ago
Solved

DATEADD cannot add 14 days

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!

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

4 Replies

  • Seanan's avatar
    Seanan
    Solution Supplier
    3 years ago

    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.

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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.

  • NatK's avatar
    NatK
    Helper I
    3 years ago

    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 djurecic '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