cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Help with Calculate/Lookupvalue Using an Effective Start Date

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.  

 

Lookupvalue Power BI.PNG

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

@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)

OutputOutput

Appreciated for providing the sample data !





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User I
Super User I

@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)

OutputOutput

Appreciated for providing the sample data !





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors