Filter the latest month for Lookupvalue

[Anonymous]

Hi,

I have a table with employee ID, their line manager ID, and their positions in many months. I would like to use Lookupvalue to create a new column in this table to get the position of  their line managers. However, position of each employee can change from month to month, and Lookupvalue cannot find the result. How can I create a Dax: when the position is not identical for all months, return the position of the latest month?

Thank you!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Your original Month column is of Text data type ("January", "February"...) and unable to get the correct latest month value. I would add another YearMonth column (202101, 202102,...) which is of Whole Number data type for comparing. You can also add a month number column if all data is within one calendar year. 

Then create below column to get the latest positions. 

Column = 
MAXX (
    FILTER (
        'Table',
        'Table'[YearMonth] = MAX ( 'Table'[YearMonth] )
            && 'Table'[ID] = EARLIER ( 'Table'[Line Manager ID] )
    ),
    'Table'[Position]
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Thank you very much @v-jingzhang 😊

[PaulDBrown]

Following @AlB request, please include actual data (not an image) to work with and a depiction of the expected output (you can create a mockup in Excel for example and post the image).

[Anonymous]

Hi, 

Data source: 

https://docs.google.com/spreadsheets/d/1SfxVbQhX4tjAKqyt5JgFhj_12Q3CWfCD/edit?usp=sharing&ouid=10969...

My expected output like this ( F column)

Thanks!

[Ashish_Mathur]

Hi,

Are you looking for a calculated column formula solution or a measure solution?

[Anonymous]

Hi @Ashish_Mathur , I would like to have a calculated column.

[Ashish_Mathur]

Hi,

I see that your question has already been answered.

[AlB]

Hi @Anonymous 

Do you want to show some sample data with the expected result?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Hi AIB,

This is sample of data, I have column Month, ID, Name, Line manger ID and Position. I create a new column named Line manger position, and instead of blank for person D, I would like to show HR Manger (his position in March). And next month, when his position changes again, it will show his new position.

Thanks!