Forum Discussion
DATEADD returning all blank values
Hello everyone, new Power BI user here.
I am attempting to create an expected date column in a table based off an initial "Join Date" and an ending "Go-Live" date, with the expected ending date for those records that don't have an ending date yet to be calculated by the average of the date difference between the existing initial and ending dates. See example below:
Maybe my syntax for the DATEADD expression is wrong, but the column returns all null values. Note that "Join Date" is a Date/Time field, and "Column" (which is calculating the average of the date difference with a DATEDIFF expression) is a Whole Number data type. "Column 2" is also a Date/Time field. Any help is greatly appreciated and I can provide more information if it is needed.
Note: I have successfully created the column that displays the correct date by adding a custom column in Power Query, but only by using the static "513" average number that I was getting. I would like a solution that is dynamic.
Hi Smiller100 ,
I think simply writing
Column2 = 'Go-Live Dates'[Join Date]+'Go-Live Dates'[Column]will do it:JoriIf I answered your question, please mark it as a solution to help other members find it more quickly.
6 Replies
Hi Smiller100 ,
I think simply writing
Column2 = 'Go-Live Dates'[Join Date]+'Go-Live Dates'[Column]will do it:JoriIf I answered your question, please mark it as a solution to help other members find it more quickly.Use EDATE() function instead, DATEADD() can only return the dates that exist in the dates column.
Very nice! Struggled with DATEADD() not working with TODAY() but EDATE() worked great. Thanks!!
Smiller100 I think you want to use DATEDIFF not DATEADD
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k thank you for the suggestion, unfortunately for this analysis I think I do want to be using DATEADD instead of DATEDIFF because I am trying to determine the expected date based off of an initial date and an average for number of days it typically takes for a record to "go-live." If there is a way to accomplish this with DATEDIFF instead of DATEADD then could you shed some light on how I would accomplish this?
Smiller100 I think it will be easier if you share a sample pbix file with the expected output.
