Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Lin Li, this was a great answer and helped me to do exactly what I wanted. Thanks a lot!