Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply

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!
0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.