Hi All!
Need help.
I want to get the last date from two tables based on a condition. Here is an example:
| per Amort | |
| DUEDATE | Amort |
| 11/16/2018 | 925,000.00 |
| 02/16/2019 | 809,375.00 |
| 05/16/2019 | 693,750.00 |
| 08/16/2019 | 578,125.00 |
| per GL | |
| GL DATE | GL |
| 11/21/2018 | 925,000.00 |
| 02/19/2019 | 809,375.00 |
| 05/17/2019 | 693,750.00 |
| 09/20/2019 | 668,189.53 |
So I have two tables namely per Amort and per GL, now I need to get the DueDate and the GL Date where the Amort is more than or equal the GL which is in this case it is 05/16/2019 and 05/17/2019, respectively.
I tried using the dax formula below but it resulted me to 09/20/2019 instead of 05/17/2019:
Last Date of Payment =
VAR diff = [Amort Trans Sum] >= [GL Trans Sum]
VAR calc = CALCULATE(MAX(' per GL'[GL DATE]),FILTER('P3 OB per GL',[Amort Trans Sum] >= [GL Trans Sum]))
VAR duedate = max('per Amort'[DUEDATE])
VAR releasedate = max('Loans'[LOAN RELEASE DATE])
RETURN
If(calc = releasedate,duedate,calc)
Thanks!
