How can I delete certain rows (or mark them as 'old information')

[Anonymous]

Dear community members, 

Every change in someone his Client_Healthcare_Track is communitated with a new message (Message_date). A change can be for example a new End_date or new Costs. The thing is, my dataset contains all information, even the messages that contain old information. I only need to know the message with the newest Message_date from each Client_Healthcare_Track (the bold ones). Does anyone know how I can eliminate the 'old records' or how I can mage a calculated column dat tells me if it is an old message or the latest message?

original dataset

Client_ID	Client_Healthcare_Track	Message_date	End_date	Costs
120684	1001	01-01-2021	31-10-2021	1000
120684	1001	15-01-2021	14-11-2021	1000
120684	1001	17-01-2021	14-11-2021	1500
120684	1001	21-01-2021	7-11-2021	1000
130783	1002	01-01-2021	31-5-2021	2500
130783	1002	07-01-2021	31-5-2021	3000

 How it should be... (strikethrough = old message II bold = latest message)

Client_ID	Client_Healthcare_Track	Message_date	End_date	Costs
120684	1001	01-01-2021	31-10-2021	1000
120684	1001	15-01-2021	14-11-2021	1000
120684	1001	17-01-2021	14-11-2021	1500
120684	1001	21-01-2021	7-11-2021	1000
130783	1002	01-01-2021	31-5-2021	2500
130783	1002	07-01-2021	31-5-2021	3000

Hope someone can help me! 

Best regards, Sander

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download example PBIX file

You can create a Calculated Column to show this

LatestMessage = IF('Table1'[Message_date] = CALCULATE(MAX('Table1'[Message_date]), FILTER('Table1', 'Table1'[Client_ID] = EARLIER([Client_ID]))), "Yes", "No")

Regards

Phil

[Anonymous]

Hi @PhilipTreacy , This solution works perfect! Cheers, Sander