Getting latest available month data from dataset

[RonitGupta]

I am having data set with different product information and cost components. Data is in rows, for one product say Product A, latest available data is July-2023 and for another (say Product B) it could be Apr-2023. 

See the sample data attached in excel file.

Key is the driver which is unique combination of all the fields. 

For product 245, we want to capture Jul month information in the costs and all the other month rows should be deleted.

For product 201, we want to capture May month information in costs and all the other months rows should be deleted. 

This is same for all the products. Jul is the latest actual month so any month after Jul need not to be considered and deleted.

[FinnCharlton · Accepted solution]

Hi @RonitGupta, here is one way to do it:

[cjaneczko · Accepted solution]

Here is one way to do it. 

[Dina · Accepted solution]

This is my solution.