Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Selecting Latest Record in Formula

I have a table with this set up:


 

 

As you can see from what I've highlighted, these are from the same company ID but over time the EMS Account Manager has changed. What we really care about is who owned the account last before the company termed which we base on the Survey Start and End Date about that EMS AM. We are calculating retention rates by AM using the company start and end dates. So in this case, this one company would be under those 3 different AM's highlighted, but we only want it to account for the latest record which is Lael Byrne. Is this possible to set up? Here are the pieces that make up our retention rate. I'm not sure what I would need to modify or if I should just take a new approach:






Thanks!

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi Anonymous,

     

    To achieve your requirement, I think you can try following measures:

     

    Count last =
CALCULATE (
    COUNT ( Company[CompanyID] ),
    FILTER (
        Company,
        Company[Survey Start Date] = MAX ( Company[Survey Start Date] )
    )
)

    Or:

     

    Count last AM =
VAR MaxSurveyStartDate =
    CALCULATE (
        MAX ( Company[Survey Start Date] ),
        ALLEXCEPT ( Company, Company[CompanyID] )
    )
RETURN
    CALCULATE (
        COUNT ( Company[CompanyID] ),
        FILTER ( Company, Company[Survey Start Date] = MaxSurveyStartDate )
    )

     

    Thanks,
    Xi Jin.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      nickchobotar So out of these 3 records, I would like to only return the one with the latest survey start/end dates. I don't really care that the accounts once belonged to Marie or Shannon. The line that I care about and would like to use in my formula for calcuating retention is Lael Byrne's. So when looking at Lael's overall retention for the companies she is the account manager for, I want this company to be included in her counts only since she was the last account manager for EMS-78701.

      There are other companies with this same situation, so when calculating the retention for each person I ideally want the Company ID to go to whoever owned the account last. Here is another example:



      So with my current formula set up, I believe company EMS-11073 would be given to both Marie and Shannon. I only want it to belong to Shannon since she has the latest Survey Start\End Dates.



      I want my retention to only apply to whoever has the latest set of survey start and end dates.


      • v-xjiin-msft's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        Hi Anonymous,

         

        To achieve your requirement, I think you can try following measures:

         

        Count last =
CALCULATE (
    COUNT ( Company[CompanyID] ),
    FILTER (
        Company,
        Company[Survey Start Date] = MAX ( Company[Survey Start Date] )
    )
)

        Or:

         

        Count last AM =
VAR MaxSurveyStartDate =
    CALCULATE (
        MAX ( Company[Survey Start Date] ),
        ALLEXCEPT ( Company, Company[CompanyID] )
    )
RETURN
    CALCULATE (
        COUNT ( Company[CompanyID] ),
        FILTER ( Company, Company[Survey Start Date] = MaxSurveyStartDate )
    )

         

        Thanks,
        Xi Jin.