Forum Discussion
How to manage date field with null values
This is one of those gotcha moments. DAX still evaluates the comparison to resolve the return type, and you end up seeing FALSE instead of BLANK(). A more fool proof way to do the comparison is by creating a custom column in the query editor.
if [Date1] = null then null else [Date1] < [Date2]Vs has this formula and we should expect for it to return blank if Custom is blank but alas it won't once the data type is changed to true/false
Super UserHi Soumeli ,
Welcome to the Power BI community! Dealing with NULL (Blank) values in dates is a classic "rite of passage" in DAX.
Here is the explanation of why your condition is passing and the best way to fix it.
The Reason ("Why is Null <= Date?")
In DAX, a Blank value is numerically treated as 0. When you compare a Number (or Date) with a Blank, DAX converts the Blank to 0 (which corresponds to the date December 30, 1899).
So, when your formula evaluates date1 <= date2:
date1 is Blank -> becomes 0 (year 1899).
date2 is 2024.
Result: 1899 <= 2024 is TRUE.
The Solution
You must explicitly handle the Blank check before the comparison logic runs. Using ISBLANK is the correct standard method, but it must be combined with your logic using AND (or &&).
Pattern 1: Return FALSE if Date1 is Blank Use this if you want the result to be "False" when the date is missing.
IsDateValid =
IF(
NOT(ISBLANK('YourTable'[date1])) && 'YourTable'[date1] <= 'YourTable'[date2],
"True",
"False"
)Logic: "If Date1 is NOT blank AND Date1 is less than Date2, then True."
Pattern 2: Return BLANK if Date1 is Blank Use this if you want the result to remain empty/null if the input is missing.
IsDateValid =
IF(
ISBLANK('YourTable'[date1]),
BLANK(),
IF('YourTable'[date1] <= 'YourTable'[date2], "True", "False")
)Note on ISDATETIME
There is no standard DAX function called ISDATETIME. You might be thinking of a function from a different language (like SQL or Excel) or perhaps you are working in Power Query (M).
Recommendation: Stick to ISBLANK() inside DAX calculated columns. It is the most performant and standard way to check for nulls.
Quick Check: Ensure your date1 column is strictly set to the Date or Date/Time data type in the ribbon. If it is set to "Text", ISBLANK might behave unpredictably (as it treats empty strings "" differently than null).
Hope this helps you tame those nulls!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.