Trying to get Minimum Value per ID from datetime

[ARomain57]

My goal is to get the value at the closest time between the Referral Date milestone and the DateTime, given some conditions I will discuss later. I have a table below that shows an example.

I created two columns in Powerbi, a Referral Milestone to DateTime and one with ABS, in case the closest time falls before the milestone. 

I am struggling with my equation. I want the Final Value with the closest DateTime within 180 minutes or less after the Referral Date or anytime before the Referral Datetime. So, anything from -x to 180. Different attributes have different conditions. Another attribute, MAP, is -x to 60 minutes. 

The value I need from the table above is 7.38, from the -6647 row, since that is the nearest value.

I created a Measure that seems to work, and when I plug in my filters, it returns the value I want, regarding the minumum time between the Referral Date and DateTime. It also filters the other attributes as expected.

MinTimeBetween Referral Milestone =

CALCULATE(

MIN('Final Table'[Referral Milestone to DateTime ABS]),

ALLEXCEPT('Final Table', 'Final Table'[Referral ID], 'Final Table'[Attribute], 'Final Table'[Referral Milestone to DateTime]))

The problem is my measure for the final value. Not sure what I am doing wrong, but it is returning a value of 7.47. I think it is returning the Max value between all values that are -x to 180. Not sure what I am doing wrong though

ValueAtMinTimeBetween Referral =

CALCULATE(

    MAX('Final Table'[Final Value]),

    FILTER(

        'Final Table',

        'Final Table'[Referral Milestone to DateTime ABS] =  [MinTimeBetween Referral Milestone]

        && 'Final Table'[Referral ID] = MAX('Final Table'[Referral ID])

    )

)

[Greg_Deckler · Accepted solution]

@ARomain57 I would the following, it's a classic Lookup Min/Max pattern: Lookup Min/Max - Microsoft Fabric Community

Measure =
  VAR __ID = MAX( 'Final Table'[Referral ID] )
  VAR __Attribute = MAX( 'Final Table'[Attribute] )
  VAR __Lowest = MINX( FILTER( ALL( 'Final Table' ), [Referral ID] = __ID && [Attribute] = __Attribute ), [Referral Milestone to DateTime ABS] )
  VAR __Final = MINX( FILTER( ALL( 'Final Table' ), [Referral Milestone to DateTime ABS] = __Lowest && [Referral ID] = __ID && [Attribute] = __Attribute ), [Final Value] )
RETURN
  __Final