Measure to replace null date with specific date

[Ben1981]

Hi, someone please help before I pull my hair out! I am trying to write a DAX measure that essentially replaces a null date value with another date value. 

I created a table visual that has 3 columns; ID, Start_Date and End_date, the source table these fields come from have a relationship from the start_date to a calander table.

I then have a slicer for the dates and for example, if I select the week commencing 23/10/2023, I get 6 rows back but all of these records have no End_Date which is fine. 

I now need to create a measure that I want to add into the table that when the end_date is null, add today()-1 else use original end_date. 

So far everything I have tried doesn't work, it either defaults all rows to today()-1 or it works but then brings in a load of rows with the start date is less or more than the week I have selected.

I'm at a loss, please help! 

Thanks,
Ben

[Ahmedx · Accepted solution]

pls try this

if ( ISBLANK(yourtable[End_date]),TODAY()-1,yourtable[End_date])
-----------or---------------------
if ( ISBLANK(MAX(yourtable[End_date])),TODAY()-1,MAX(yourtable[End_date]))

[Ben1981]

Hi thanks for the help guys but I couldn't find the solution I wanted so I ended up going back to the drawing board and I re-designed my model which seems to have done the trick and it's working the way I wanted it too.

Most of the working being upstream so I didn't need to worry about the DAX.

Thanks for the help as always, would be lost without these forums! haha

Ben

[v-zhengdxu-msft · Accepted solution]

Hi @Ben1981 

Maybe you can try this:

First of all, I created a sample:

Transform Data > (Right-Click the null) Replace Values.

Insert null(or other values that don't affect the report)

Change the second null into

Date.AddDays(DateTime.LocalNow(),-1)
//Use M Query to return the date/time yesterday.

Like this:

Then change the column type to Date.

The result is as follow:

Documentation on M Query:

Power Query M function reference - PowerQuery M | Microsoft Learn

Time Queries:

DateTime functions - PowerQuery M | Microsoft Learn

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahmedx · Accepted solution]

pls try this

if ( ISBLANK(yourtable[End_date]),TODAY()-1,yourtable[End_date])
-----------or---------------------
if ( ISBLANK(MAX(yourtable[End_date])),TODAY()-1,MAX(yourtable[End_date]))

[Ben1981]

DIdn't work, it just added today-1 for each row plus for ones with the start date not within my selected week.

This is the result I get, if I use the DAX you suggested. 

My slicer is set to 01/10/2023 so I want it to show me...

or if I change the slicer to 06/06/1988 then...

[Ahmedx]

show how you wrote it, or share the file to help you