Forum Discussion
Find most recent value based on two criteria
I have two datasets. Dataset 1 (transactions) has the following columns: Month, client, transactions Dataset 2 (consultants) has the following columns: Client, consultant, date of start ...
HI, Anonymous
Sorry for my careless and misunderstanding for the issue,
and you may try to this formula as below:
Consultant = MAXX ( TOPN ( 1, FILTER ( consultants, results[Client] = consultants[Client] && results[Date] >= consultants[Starting date] ), consultants[Starting date], DESC ), consultants[Consultant] )Regards,
Lin Li
Hi and thanks for your suggestion, I really appreciate all the ideas and input. However I don't think that your solution works for what I'm trying to achieve. The result should be that the right consultant is returned for the right date and client.
The table should be something like this:
Date Client Transactions Consultant (calculated column)
| Date | Client | Transactions | Consultant (calculated column) |
| 1-1-2019 | Client A | 2000 | Consultant 1 |
| 1-1-2019 | Client B | 1500 | Consultant 2 |
| 3-1-2019 | Client C | 4387 | Consultant 3 |
| 1-6-2019 | Client A | 2433 | Consultant 2 |
v-lili6-msft
Community Support
Community SupportHI, Anonymous
Sorry for my careless and misunderstanding for the issue,
and you may try to this formula as below:
Consultant =
MAXX (
TOPN (
1,
FILTER (
consultants,
results[Client] = consultants[Client]
&& results[Date] >= consultants[Starting date]
),
consultants[Starting date], DESC
),
consultants[Consultant]
)Regards,
Lin Li
Hi Lin Li, this was a great answer and helped me to do exactly what I wanted. Thanks a lot!