Forum Discussion

Jkaelin's avatar
Jkaelin
Resolver I
6 years ago
Solved

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 Exc...
Need Help
  • PattemManohar's avatar
    PattemManohar
    6 years ago

    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 !

PattemManohar's avatar
PattemManohar
Community Champion
6 years ago

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's avatar
Jkaelin
Resolver I
6 years ago

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