Forum Discussion

Soumeli's avatar
Soumeli
Regular Visitor
8 months ago
Solved

How to manage date field with null values

Hi Team,   I am very new in Power BI. I need to create a calculated column where I am comparing two date fields eg. date1<= date2. date1 has null values and it is passing the condition. What is the...
Need Help
  • danextian's avatar
    danextian
    8 months ago

    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

     

     

HarishKM's avatar
HarishKM
Super User
8 months ago

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.