topic Re: create new column to mirror existing date column and set value for any nulls in DAX Commands and Tips

create new column to mirror existing date column and set value for any nulls

craig615 — Wed, 20 Apr 2022 16:50:07 GMT

The current SQL dataset I am working on has several null or blank entries under one of my date columns as they technically have no set end date for their project completion. I am working on Gannt chart visual. However, since some items are blank or null nothing is rendered for these items on the visual. My intent is to create a new column that will copy the valid values for the end date column, and replace any blanks or nulls with a value set five years out from todays date or Jan 1 of whatever the current year is. I also want to ensure that should a value be added later on in the SQL database that once I refresh my report it will include the new date and not hold on to the previous date stamp. Does anyone know if this is possible or perhaps if there is an easier method of doing this?

Re: create new column to mirror existing date column and set value for any nulls

SpartaBI — Wed, 20 Apr 2022 17:27:18 GMT

It is possible like you said in DAX in the model.
It's also possible in Power Query to replace null with whatever value or add a column to check like in the model with DAX. In Power Query you got GUI for these operations.
The best options is alwyas to push it as back as you can to the the data source, as in do the change there in a view.
But all options are valid and possible.

In dax, you just need to create a new cloumn like:
IF(
isblank('your_table'[your_date_cloumn]),
today(),
'your_table'[your_date_column]
)

Re: create new column to mirror existing date column and set value for any nulls

craig615 — Wed, 20 Apr 2022 17:55:15 GMT

This is great thanks for the help . This is really really close. It created a new column no problem. It copied all the good data from the first column over perfectly. It entered a value for any nulls or missing data, awesome. The only thing missing now is that it set all the nulls to today. Do you know what the syntax would be to push it out 5 years or realistically any specified allotment of time? Once again thanks for the help so far I really appreciate it.

Re: create new column to mirror existing date column and set value for any nulls

SpartaBI — Wed, 20 Apr 2022 18:01:46 GMT

Sure, DATEADD is your friend.
Check this:
https://dax.guide/dateadd/