How to obtain the last status of an item by date

[davidestg]

HI: 

I have a list of assets (called from A to E) that can be in differents status (Manufactured, Installed, ExitInstallation) by date as follows: 

Asset	Status	Date
A	Associated	martes, 21 de agosto de 2018
B	Associated	martes, 21 de agosto de 2018
C	Manufactured	martes, 14 de agosto de 2018
D	Manufactured	martes, 14 de agosto de 2018
E	Manufactured	martes, 14 de agosto de 2018
E	ExitInstallation	viernes, 19 de octubre de 2018
C	ExitInstallation	miércoles, 10 de octubre de 2018
E	ExitInstallation	lunes, 5 de noviembre de 2018
C	ExitInstallation	lunes, 5 de noviembre de 2018
D	Manufactured	lunes, 5 de noviembre de 2018
A	Associated	lunes, 5 de noviembre de 2018
B	Associated	lunes, 5 de noviembre de 2018

We need to obtain the last status to each assets as follows: 

Status                                                     Count Assets

Associated	2
Manufactured	1
ExitInstallation	2

Related with date filter to know in whic status was an asset or all of them. 

As well to represent an histogram to view 1 asset wich status has had during the time. 

Could you help me? 

BR

David

[Zubair_Muhammad · Accepted solution]

@davidestg

One way could be to add this calculated column which will flag the last dates for each asset

Then you can use the sum of this column

Calc Column =
IF (
    [Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Asset] ) ),
    1
)

 

[davidestg]

Excellent solution, it runs ok!! 

You are a crack! 

Thanks, 

David