cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Selecting Latest Record in Formula

I have a table with this set up:


1.PNG

 

 

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:

1.PNG
1.PNG
1.PNG
1.PNG

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: Selecting Latest Record in Formula

Hi @mrainey,

 

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 )
    )

1.PNG

 

Thanks,
Xi Jin.

4 REPLIES 4
nickchobotar Established Member
Established Member

Re: Selecting Latest Record in Formula

@mrainey

 

What exactly are you trying to return ?

 

N-

mrainey Member
Member

Re: Selecting Latest Record in Formula

@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:

1.PNG

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.

1.PNG

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


v-xjiin-msft Super Contributor
Super Contributor

Re: Selecting Latest Record in Formula

Hi @mrainey,

 

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 )
    )

1.PNG

 

Thanks,
Xi Jin.

mrainey Member
Member

Re: Selecting Latest Record in Formula

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

1.PNG

 

and add the additional piece to it:

1.PNG

 

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.