Finding amount based on latest date in 2nd table

[fahadarshad]

Hello everyone,

I am new to Power BI and this is my first post on this community.

Here is my Q.

I have two tables

Table1			Table 2
ID	Result		ID	Amount	Date
1			1	5	Wednesday, January 04, 2017
2			1	10	Thursday, January 05, 2017
3			2	15	Friday, January 06, 2017
4			2	5	Saturday, January 07, 2017
5			3	25	Sunday, January 08, 2017
			4	50	Monday, January 09, 2017
			5	60	Tuesday, January 10, 2017
			5	100	Wednesday, January 11, 2017
Result into Table1
ID	Result
1	10
2	5
3	25
4	50
5	100

[Phil_Seamark]

Hi @fahadarshad

This worked for me on your test data.  I created the following calculation, which takes advantage of the relationship between Table1 and Table2

My Amount = CALCULATE(
			MAX('Table2'[Amount]),
			LASTDATE('Table2'[Date])
			)

[fahadarshad]

Hi Phil

I am getting an error saying "a date column containing duplicate dates was specified in the call to the function."

[Phil_Seamark]

Are you loading the measure on your sample dataset, or on a larger dataset?

[fahadarshad]

Hi Phil

It is on a larger dataset.

[Phil_Seamark]

Hi @fahadarshad

Any chance you can share a bigger sample so I can try and replicate?

[fahadarshad]

I can share with you the entire dataset. Here you go

https://www.dropbox.com/s/kxhsssa9wp0tpyt/Book1.xlsx?dl=0

[Phil_Seamark]

This doesnt throw an error.

My Amount = CALCULATE(
			MAX('Table2'[Amount - Quote]),
			filter('Table2',
				'Table2'[Created On]=MAX('Table2'[Created On])
				)
			)

Just checking if it is right

[fahadarshad]

it doesn't throw an error, but doesnt throw any values out either.

[Phil_Seamark · Accepted solution]

Hi @fahadarshad

Please see if this PBIX file works for you

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestacc...

[fahadarshad]

Thank you!!!!!