How to manage date field with null values

Soumeli 

Calculated column:

Flag =
IF (
NOT ISBLANK ( 'Table'[date1] )
&& 'Table'[date1] <= 'Table'[date2],
1,
0
)

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

Hi 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.

Soumeli Hey,

create a new date comparision column and refer below dax measure to create a new one.

Date_Comparison =
VAR d1 = 'Table'[date1]
VAR d2 = 'Table'[date2]
RETURN
IF( ISBLANK(d1) || ISBLANK(d2), FALSE(), d1 <= d2 )

If your date1 is text or uses “empty string”/0 sentinel values, broaden the check:

• IF( d1 = BLANK() || d1 = "" || d1 = 0, FALSE(), DATEVALUE(d1) <= d2 )

Power Query alternative (before loading to the model):

• Add a custom column: each [date1] <> null and [date2] <> null and [date1] <= [date2]
• Or filter out nulls in date1/date2.

Notes:

• There’s no ISDATETIME in DAX; that’s a Power Query (M) function. ISBLANK works only for true DAX BLANKs, not empty strings, 0, or 1900‑01‑01 placeholders. Ensure both columns are typed as Date/DateTime in the model.

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.

Please try the formula below:

Result =
IF (
    NOT ISBLANK ( 'Table'[date1] )
        && 'Table'[date1] <= 'Table'[date2],
    TRUE (),
    FALSE ()
)

Hi Soumeli,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Kedar_Pande, cengizhanarslan, HarishKM & danextian , thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

Hi @Soumeli,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support