Forum Discussion

waldnerr's avatar
waldnerr
Helper I
5 years ago
Solved

Lookup latest value

Hi,   i would like create a calculated column called "Program Name" in the "Global Project" for each GlobalProjectKey. The relevant value is stored in the table "Custom Fields", column "Program Na...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi waldnerr,

     

    You can try this new column, it is a little long.

    Program Name =
var LastestDate = 
CALCULATE(
    MAX('Project Period'[Datekey]),
    FILTER('Project Period',[GlobalProjectKey]=EARLIER('Global Project'[Globalprojectkey]))
)
var LastestKey = 
CALCULATE(
    MAX('Project Period'[ProjectPeriodKey]),
    FILTER('Project Period',[Globalprojectkey]=EARLIER('Global Project'[Globalprojectkey]) && [Datekey]=LastestDate)
)
RETURN
CALCULATE(
    MAX('Custom Fields'[Program Name]),
    FILTER('Custom Fields',[Globalprojectkey]=EARLIER('Global Project'[Globalprojectkey]) && [ProjectperiodKey]=LastestKey)
)

     

    Here's the file I made with your data, you can try it.

     

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.