Forum Discussion
Measure to replace null date with specific date
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
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]))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.
5 Replies
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]))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...
show how you wrote it, or share the file to help you
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.
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
