Lookup latest value

[waldnerr]

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 Name". 

I would like to select the value with the latest "Date Key" value from the "Project Period" table for each "Globalprojectkey" value.

"Global Project" table:

Globalprojectkey	Program Name
A	Program A
B	Program C

1: N (via GlobalProjectKey)

"Project Period" table:

GlobalProjectKey	Datekey	ProjectPeriodKey
A	08/2021	Key 1
A	07/2021	Key 2
B	08/2021	Key 3
B	07/2021	Key 4
B	06/2021	Key 5

1:N (via ProjectPeriodKey)

"Custom Fields" table:

GlobalProjectKey	ProjectperiodKey	Program Name	Other Column A	Other Column B
A	Key 1	Program A
A	Key 1		Other 0
A	Key 2		Other 1
A	Key 2			Other 2
A	Key 2	Program B
B	Key 3	Program C
B	Key 4	Program D
B	Key 5	Program E

I tried to solve this via the LOOKUPVALUE function but wasn't sucessfull, because i was not to able to select the value with the latest datekey per project from the project period table.

I would apprechiate your help to find a solution.

Thank you,

Best regards,

Reinhard

[v-chenwuz-msft · Accepted solution]

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.

[waldnerr]

@v-chenwuz-msft 

Thank you, your proposed solution works perfectly.

@Greg_Deckler 

Thank you too for your valuable input.

[Greg_Deckler]

@waldnerr Seems like you want Lookup Min/Max - Microsoft Power BI Community