Forum Discussion

Shinyl's avatar
Shinyl
New Member
2 years ago
Solved

Revenue in max date

I have 2 tables

Ddate

DateMonthMonth Year
4/1/20244April 2024
3/1/20243Mar 2024
4/28/20244April 2024
3/3/20243Mar 2024
4/27/20244April 2024
4/29/20244April 2024
4/30/20244April 2024

Revenue

ValueRecord DateCustomer
303/1/2024A
1003/1/2024C
2004/27/2024A
304/28/2024A
604/27/2024C
804/27/2024B
404/28/2024B
503/1/2024B
904/28/2024C

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!

Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Shinyl Try:

     

     

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

     

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Shinyl Try:

     

     

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