Lookup with IF Condition for a Date columns

[Anonymous]

Hello,

I need help to add the Calculated Recovery Date in the Table 2 using DAX command.

Logic for Recovery Date: First it should pick the Table2[Rec Date], if Table2[Rec Date] is Null, then do a lookup on Table1[AccountingDate] and pick the MAX Table2[RecDate] group by AccountID.

Table1:

Account ID	AccountingDate
1	01-02-2020
1	01-03-2020
2	01-04-2020
2	01-05-2020
3	01-06-2020
4	01-07-2020
5	01-08-2020
6	01-05-2020
7	01-06-2020
7	01-07-2020

Table2:

Account ID	RecDate	RecoveryDate
1	01-02-2021	01-02-2021
2		01-05-2020
3	01-04-2021	01-04-2021
4		01-07-2020
5	01-06-2021	01-06-2021
6	01-07-2021	01-07-2021
7	01-08-2021	01-08-2021

[Anonymous]

Hi @Anonymous ,

@tackytechtom 's calculated column is good.

You could also create a measure.

Calculated Recovery Date = IF(ISBLANK(MAX('Table2'[RecDate])),MAX('Table1'[AccountingDate]),MAX('Table2'[RecDate]))

Best Regards,

Stephen Tao

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

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about:

Calculated Column =
IF ( Table2[RecDate] = BLANK(), MAXX ( FILTER ( Table1, Table1[Account ID] = Table2[Account ID]), Table1[AccountingDate] ), Table2[RecDate] )

Does this work? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/