IF Column with dates

[JamesGordon]

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 · Accepted solution]

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]

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!