Last updated Status for each employee based on timestamp

[vasu6811]

Hi,

In my table, I insert employee Id, Status and timestamp with Date. Status can be can be updated frequently (means it can be updated hourly). 

How to identify latest Status on an employee on given Date?

[GilbertQ]

Hi @vasu6811

What you could do, is to create a new measure which will be for the MAX of the Status.

For example:

Latest Status = MAX('TableName'[Status])

[vasu6811]

Hi guavaq,

I have tried your solution. Here problem is Status contains in different table. In employee transaction table, it contains only Status id. I have applied measure on status id. I am getting max of status id. But I expecting last updated status?

Here is sample data

EmployeeId	StatusID	Datestamp
1	1	2017-05-01 11:30:00
1	3	2017-05-01 11:35:00
1	2	2017-05-01 11:40:00
2	3	2017-05-01 11:30:00
2	2	2017-05-01 11:35:00
2	1	2017-05-01 11:40:00

I am expecting result in following format

EmployeeId	StatusID	Datestamp
1	2	2017-05-01 11:40:00
2	1	2017-05-01 11:40:00

[GilbertQ · Accepted solution]

Hi there

I got it working with the following measures below.

This first one is to get the Latest Status

Latest Status = CALCULATE(MAX('Data'[Datestamp]),ALLSELECTED('Data'[Datestamp]))

The next one is to get the Latest Status ID

Latest Status ID = CALCULATE(MAX('Data'[StatusID]),FILTER('Data','Data'[Datestamp] = MAX('Data'[Datestamp])))

And this is what it looks like when I created it in a table

[vasu6811]

Hi guavaq,

Thanks for solution. It worked for me.