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 ab...
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.

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
Solution 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.