Revenue in max date

[Shinyl]

I have 2 tables

Ddate

Date	Month	Month Year
4/1/2024	4	April 2024
3/1/2024	3	Mar 2024
4/28/2024	4	April 2024
3/3/2024	3	Mar 2024
4/27/2024	4	April 2024
4/29/2024	4	April 2024
4/30/2024	4	April 2024

Revenue

Value	Record Date	Customer
30	3/1/2024	A
100	3/1/2024	C
200	4/27/2024	A
30	4/28/2024	A
60	4/27/2024	C
80	4/27/2024	B
40	4/28/2024	B
50	3/1/2024	B
90	4/28/2024	C

They are both connected by Date-Record date

I want to create a measure for revenue value at the maximum date in Revenue table, ignoring the Monthyear filter

For example, if i selected Mar 2024, and customer C, I want to show the revenue on the max date (04/28/2024), which is 90.

when i used 

MAXX(ALLSELECTED(Revenue[RecordDate]),Revenue[RecordDate])) the max date is correct.

but when I apply this max date to calculate the sum of revenue, it does not show the expected value:

 CALCULATE(SUM(Revenue[Value]), FILTER(DDate,DDate[Date] =MAXX(ALLSELECTED(Revenue[RecordDate]),Revenue[RecordDate]))

Thank you in advance!

[Greg_Deckler · Accepted solution]

@Shinyl Try:

Measure =
  VAR __MaxDate = MAXX(ALL(Revenue[RecordDate]),Revenue[RecordDate]))
  VAR __Result = SUMX( FILTER( ALLEXCEPT('Revenue', [Customer]), [RecordDate] = __MaxDate ), [Value] )
RETURN
  __Result

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Greg_Deckler · Accepted solution]

@Shinyl Try:

Measure =
  VAR __MaxDate = MAXX(ALL(Revenue[RecordDate]),Revenue[RecordDate]))
  VAR __Result = SUMX( FILTER( ALLEXCEPT('Revenue', [Customer]), [RecordDate] = __MaxDate ), [Value] )
RETURN
  __Result