Forum Discussion

Peejz_Jalmasco's avatar
Peejz_Jalmasco
Icon for Helper I rankHelper I
6 years ago

Generate last date base on a condition

Hi All!

Need help.

I want to get the last date from two tables based on a condition. Here is an example:

 
per Amort 
DUEDATEAmort
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 DATEGL
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!
Need Help
No RepliesBe the first to reply