Forum Discussion
Lookup with IF Condition for a Date columns
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 |
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/
2 Replies
- tackytechtom
Most Valuable Professional
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/ 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.