Help with Calculate/Lookupvalue Using an Effective Start Date

[Jkaelin]

Good morning -

Problem:  Unable to correctly solve my calculated column with the corresponding benchmark id.  

Details:  Picture is a thousand words.  Below is a sample set of data within Excel that displays my two data sources.  There is no relationship betweeen these two tables.  The 'yellow' column is the column I am trying to derive the calculated column {not a measure}.  

Notes:  Please note that the 'Effective_Date' is what determines when a new benchmark Starts & Ends.  For example:  Account 6 uses Benchmark Id 5 until 1/1/2018, in which the new Benchmark Id 7 would be effective.  

I've tried various LOOKUPVALUE functions & this several versions of this function:  

CALCULATE(

            COUNT('Dim_Account_to_Benchmark'[Benchmark_ID]),

            FILTER('Dim_Account_to_Benchmark',

            'Historical_Holdings'[As_Of_Date]<=MAX('Dim_Account_to_Benchmark'[Effective_Date]) &&

            'Historical_Holdings'[As_Of_Date]>=MIN('Dim_Account_to_Benchmark'[Effective_Date])

            ))

Question: Any tips or advice on how to derive a calculated column that can lookup the corresponding Benchmark ID based on the associated account & effective date?

Vibrant Regards - James

[PattemManohar · Accepted solution]

@Jkaelin  Please try this as a New Column.

BenchmarkID = 
VAR _Date = MAXX(FILTER(Test03Lkp,Test03Data[AccountID]=Test03Lkp[AccountID] && Test03Lkp[EffectiveDate] <= Test03Data[AsOfDate]),Test03Lkp[EffectiveDate])
RETURN LOOKUPVALUE(Test03Lkp[BenchmarkID],Test03Lkp[AccountID],Test03Data[AccountID],Test03Lkp[EffectiveDate],_Date)

Output

Appreciated for providing the sample data !

[Jkaelin]

@PattemManoharSuper helpful.  Thank you very much.  I couldn't figure this out & spend hours trying too.  Thanks again!