Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
davidestg
Helper I
Helper I

How to obtain the last status of an item by date

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: 

 

AssetStatusDate
AAssociatedmartes, 21 de agosto de 2018
BAssociatedmartes, 21 de agosto de 2018
CManufacturedmartes, 14 de agosto de 2018
DManufacturedmartes, 14 de agosto de 2018
EManufacturedmartes, 14 de agosto de 2018
EExitInstallationviernes, 19 de octubre de 2018
CExitInstallationmiércoles, 10 de octubre de 2018
EExitInstallationlunes, 5 de noviembre de 2018
CExitInstallationlunes, 5 de noviembre de 2018
DManufacturedlunes, 5 de noviembre de 2018
AAssociatedlunes, 5 de noviembre de 2018
BAssociatedlunes, 5 de noviembre de 2018

 

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

 

Status                                                     Count Assets

Associated2
Manufactured1
ExitInstallation2

 

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

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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
)

howto.png 


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@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
)

howto.png 


Regards
Zubair

Please try my custom visuals

Excellent solution, it runs ok!! 

 

You are a crack! 

 

Thanks, 

David

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.