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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Greg,

 

This is an example of the data (can't share the real thing). Should've mentioned I was trying to achieve this in Power Query. I don't know if the composite models are availabe there:

 

 

 

 

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

hi, Anonymous

After my research, you could try this formula:

result = 
SUMX (
    TOPN (
        1,
        FILTER (
            SUMMARIZE (
                results,
                results[Client],
                results[Date],
                "_transaction", CALCULATE ( SUM ( results[Transactions] ) )
            ),
            results[Client] = consultants[Client]
                && results[Date] <= consultants[Starting date]
        ),
        results[Date], DESC
    ),
    [_transaction]
)

Result:

 

here is pbix, please try it.

https://www.dropbox.com/s/n8rpg99uxh4m7iq/Find%20most%20recent%20value%20based%20on%20two%20criteria.pbix?dl=0

 

Best Regards,

Lin