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.

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.