cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors