Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
craig615
Regular Visitor

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

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?

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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]
)

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
Community Champion

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

SpartaBI
Community Champion
Community Champion

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]
)

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors