Selecting Latest Record in Formula

[Anonymous]

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!

[nickchobotar]

@Anonymous

What exactly are you trying to return ?

N-

[Anonymous]

@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 · Accepted solution]

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.

[Anonymous]

@v-xjiin-msft This works great! I just don't know how to apply it to my formulas. When I take this formula:

and add the additional piece to it:

It takes the last survey start date in my table for all companies rather than by each company. It went from 90 active companies total to just 1.

But the second formula you suggested works like I would want it to, again I just don't know how to apply it to my formulas.