Forum Discussion

JamesGordon's avatar
JamesGordon
Helper II
6 years ago
Solved

IF Column with dates

Hi,

 

I am trying to create a column that will return the date from two columns if one is blank.

 

The two columns are Trans Date and Sett Date. Trans Date always has a date however i would like to use Sett Date unless it is Blank?

 

I have tried to create a column using this Dax

Sett Date V2 = IF(Sheet1[SETT_DATE] > Sheet1[TRANS_DATE], Sheet1[SETT_DATE], Sheet1[TRANS_DATE])
 
However i get this error "DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."
 
Here is a screenshot of the data with the desired outcome in the red box:
Can anyone suggest a DAX that may work?
 
 
  • ibarrau's avatar
    ibarrau
    6 years ago

    Hi. The error seems to be about one of those columns. Check their data type because one of them might be type text and the other date. Make sure they are both date (change them in the edit queries / transform data menu). You can read more about this here:

    https://docs.microsoft.com/en-us/power-bi/desktop-data-types

    After having the same data types your dax should work. However you mention you want to ask about blank, so you should try this:

     

    NewColumn = 
IF(
    ISBLANK(Table[SETT_DATE]),
    Table[TRANS_DATE],
    Table[SETT_DATE]
)

     

    Hope this helps,

2 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. The error seems to be about one of those columns. Check their data type because one of them might be type text and the other date. Make sure they are both date (change them in the edit queries / transform data menu). You can read more about this here:

    https://docs.microsoft.com/en-us/power-bi/desktop-data-types

    After having the same data types your dax should work. However you mention you want to ask about blank, so you should try this:

     

    NewColumn = 
IF(
    ISBLANK(Table[SETT_DATE]),
    Table[TRANS_DATE],
    Table[SETT_DATE]
)

     

    Hope this helps,

    • JamesGordon's avatar
      JamesGordon
      Helper II
      6 years ago

      Thanks ibarrau you were correct. One of the columns wasnt set as a date in the data type!

      once i changed that my Dax worked!

       

      Thanks!