Identifying a MAX value from other rows that are less than row value

[feedbackisagift]

Hi, could someone help me figure out how to write a dynamic measure that looks at any remaining records when filtering and determines the max of the remaining records relative to it's own value?  I've attempted various things such as:

Max_Of_Other_Stages = CALCULATE(MAX(OppHistory[StageNameValue]),ALLEXCEPT(OppHistory,OppHistory[StageNameValue]))

This is not behaving as expected since it always returns 11.

This is a one step toward the true goal which is to calculate the days between those two records, each row's date vs. the max of the records that have a stage value < its own.

Any help will be much appreciated!

[Eric_Zhang · Accepted solution]

@feedbackisagift

Add a filter to the DAX formula.

Max_Of_Other_Stages_In_The_Same_Opportunity =
CALCULATE (
    MAX ( OppHistory[StageNameValue] ),
    FILTER (
        ALLEXCEPT ( OppHistory, OppHistory[OpportunityID] ),
        OppHistory[StageNameValue] < MAX ( OppHistory[StageNameValue] )
    )
)

[feedbackisagift]

Hi Eric,

I noticed that this does not respect filters that have been selected...  For example if I exclude stage name value 4, then I would expect the value for anything StagenameValue of 6 to recognize that the max of the records below it has now become the -1 value.

Do you know how to do this?

Thanks,

Joaquin

[feedbackisagift]

Thanks Eric.  I was also able to come up with a similar alternative...

Max_Of_Other_Stages = if(HASONEVALUE(OppHistory[StageNameValue]),
CALCULATE(MAX(OppHistory[StageNameValue]),
ALLEXCEPT(OppHistory,OppHistory[StageNameValue]),
OppHistory[StageNameValue]<values(OppHistory[StageNameValue])),
blank())