cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkaelin Member
Member

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
Super User

Re: Help with Calculate/Lookupvalue Using an Effective Start Date

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

image.pngOutput

Appreciated for providing the sample data !



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

Proud to be a Datanaut !





View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Help with Calculate/Lookupvalue Using an Effective Start Date

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

image.pngOutput

Appreciated for providing the sample data !



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

Proud to be a Datanaut !





View solution in original post

Jkaelin Member
Member

Re: Help with Calculate/Lookupvalue Using an Effective Start Date

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)