Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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])
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 |
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
Hi guavaq,
Thanks for solution. It worked for me.