Help with Calculate/Lookupvalue Using an Effective Start Date

Question

Good morning -&nbsp;Problem:&nbsp; Unable to correctly solve my calculated column with the corresponding benchmark id. &nbsp;&nbsp;Details:&nbsp; Picture is a thousand words.&nbsp; Below is a sample set of data within Excel that displays my two data sources.&nbsp; There is no relationship betweeen these two tables.&nbsp; The 'yellow' column is the column I am trying to derive the calculated column {not a measure}. &nbsp;&nbsp;Notes:&nbsp; Please note that the 'Effective_Date' is what determines when a new benchmark Starts &amp; Ends.&nbsp; For example:&nbsp; Account 6 uses Benchmark Id 5 until 1/1/2018, in which the new Benchmark Id 7 would be effective. &nbsp;&nbsp;I've tried various LOOKUPVALUE functions &amp; this several versions of this function: &nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT('Dim_Account_to_Benchmark'[Benchmark_ID]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER('Dim_Account_to_Benchmark',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Historical_Holdings'[As_Of_Date]&lt;=MAX('Dim_Account_to_Benchmark'[Effective_Date]) &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Historical_Holdings'[As_Of_Date]&gt;=MIN('Dim_Account_to_Benchmark'[Effective_Date])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ))&nbsp;Question: Any tips or advice on how to derive a calculated column that can lookup the corresponding Benchmark ID based on the associated account &amp; effective date?&nbsp;Vibrant Regards - James&nbsp;

pattemmanohar · Accepted Answer

Jkaelin&nbsp; Please try this as a New Column.
&nbsp;
BenchmarkID = 
VAR _Date = MAXX(FILTER(Test03Lkp,Test03Data[AccountID]=Test03Lkp[AccountID] &amp;&amp; Test03Lkp[EffectiveDate] &lt;= Test03Data[AsOfDate]),Test03Lkp[EffectiveDate])
RETURN LOOKUPVALUE(Test03Lkp[BenchmarkID],Test03Lkp[AccountID],Test03Data[AccountID],Test03Lkp[EffectiveDate],_Date)
Output
Appreciated for providing the sample data !

Forum Discussion

Help with Calculate/Lookupvalue Using an Effective Start Date

2 Replies

Related content

Make Parameter effective using calculated column

Interesting effects with calendar-based time intelligence

Writing LOOKUPVALUE Using CALCULATE

Calculated columns: USERELATIONSHIP or LOOKUPVALUE?

Embed - Effective Identity