Find the last date in a table and show a relative column in another table.

[Portrek]

Hello everybody, I have a problem.

I have two tables, the first is updated every month sequencelly like the image below and show the situation for employees.

The second table is fixed, and stores employee datas. like the secound image.

I need the "Situation" field (blue) to always be updated with the last value put in the first table. (red mark). in this case the result has been - remote / remote / sick / ok

How can i do that. Any help is greatly appreciated.

Thanks in advanced.

[V-pazhen-msft · Accepted solution]

@Portrek 

I agree that a calculate column should do.

Column = CALCULATE(MAX('Table'[Situation]),LASTDATE('Table'[Date]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Portrek 

Please check the below.

I created a sample pbix file based on the explanation.

If you have relationships with different table, then it the measure might need to be amended.

Based on the information that is provided, the below works fine.

https://www.dropbox.com/s/t8m7m00dtccmjy3/portrek.pbix?dl=0 

Situation Measure =
IF (
ISFILTERED ( 'Table'[ID number] ),
LASTNONBLANKVALUE ( 'Table'[Date], SELECTEDVALUE ( 'Table'[Situation] ) )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Portrek]

Hello, Jihwan_Kim.

Thank you for your help, but unfortunately it did not work for me, Mesure could not return the values.

Basically, I have two tables in my PBI, the first has the information of the employees, such as names, age, address, identification, etc. And at the second table I have information on the situation of these employees as removed, sick, licensed, retirees, etc.

The first table is fixed, is only updated when a new employee is hired. The second table is updated monthly with the current situation of the employee. So I intend to show among my data the total number of employees and the current quantitative according to the situation of the moment.

So I have to tell the PBI to locate the latest date in Table 2, compare the records (ID) and return the value of the collune situation. Something like a lookupvalue considering the last date of the calendar column.

I think a calculated column that brought me; Latest information from the column situation of Table 2 would solve my problem, but I still have not found a way to build this ..

A common key between the tables are the identification numbers.

Anyway, thank you for the support.