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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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)

    DateClientTransactionsConsultant (calculated column)
    1-1-2019Client A2000Consultant 1
    1-1-2019Client B1500Consultant 2
    3-1-2019Client C4387Consultant 3
    1-6-2019Client A2433Consultant 2
    • 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!