LOOKUPVALUE that search for the latest value

[req77]

Hello,

I have two tables that follow this structure:

Table1

ID	Name	Position	Year-Month
1	Jack	Operator	2021-01
1	Jack	Cleaner	2021-02
2	Elise	Director	2021-01
2	Elise	Director	2021-02
3	Audrey	Mechanic	2021-01
3	Audrey	Electrical maintenance	2021-02
3	Audrey	Mechanic	2021-03
4	Leo	Web developer	2021-05

Table2

ID	Name
1	Jack
2	Elise
3	Audrey
4	Leo

I want to create a calculated column in Table2 that uses the ID column in both tables to searchs in Table1 for each person's most recent Position, based on Table1's Year-Month column. The expected result would be this:

ID	Name	Position
1	Jack	Cleaner
2	Elise	Director
3	Audrey	Mechanic
4	Leo	Web developer

Can someone help me?

[VahidDM · Accepted solution]

Hi @req77 

You can summarize your Table 1 and create a new Table with the desired data, try this code to add a new table:

Table 2=
SUMMARIZE (
    'Table 1',
    'Table 1'[ID],
    'Table 1'[Name],
    "Position",
        CALCULATE (
            MAX ( 'Table 1'[Position] ),
            FILTER ( 'Table 1', 'Table 1'[Year-Month] = MAX ( 'Table 1'[Year-Month] ) )
        )
)

Output:

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

Appreciate your Kudos!!

[charleshale]

@VahidDM you seem good with SUMMARIZE.  Any thoughts on this Forum question?  

[Jihwan_Kim]

Hi, please check the attached pbix file down below.

Latest position CC =

VAR latestdate =

MAXX ( RELATEDTABLE ( Table1 ), Table1[monthenddate CC] )

VAR currentid = Table2[ID]

RETURN

MAXX (

FILTER (

Table1,

Table1[ID] = currentid

&& Table1[monthenddate CC] = latestdate

),

Table1[Position]

)