IF date is blank, pull date from different table

[megg8]

i have 2 tables with target closure dates. 

I'm trying to write a DAX/formula IF statement that will go "if [tableA] target closure date is blank, populate with [tableB] target closure date, else [tableA] target closure date. 

I tried the below, but kept getting "Expressions that yield variant data-type cannot be used to define calculated columns."

true target close date = IF(ISBLANK('TableA'[Target Closure Date]), CALCULATE(MAX(TableB[Target Closure Date])), 'TableA'[Target Closure Date])

Not sure what to do

[v-kongfanf-msft]

Thanks for the reply from @vicky_ , please allow me to provide another insight:

Hi  @megg8 ,

You can try formula like below and create calculated column in tableA:

True Target Close Date =
IF (
    ISBLANK ( 'TableA'[Target Closure Date] ),
    MAXX ( 'TableB', 'TableB'[Target Closure Date] ),
    'TableA'[Target Closure Date]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

Is the data type the same? i.e both Table A closure date and Table B closure date are actually date type (and one of them isn't a number / string)... Otherwise, the issue might be that your column name starts with True, which might be a reserved word (i don't know for sure, because i can't find the docs for this)

[megg8]

that was the problem, one of my dates had a data type of text. 

[v-kongfanf-msft · Accepted solution]

Hi @megg8 ,

If it's a date field, then just change it from text type to date type and then try the formula as above to see if it works?

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.