Get the most recent value with multiple filters

[Anonymous]

I'm looking to create a report where I can get the most recent selling price and unit cost of each item by department , customer & brand. I just require the value & not the aggregate. 

For eg. Brand A has 3 products in Department A and the last Prod x was sold on 28th , Prod y sold on 29th and Prod z sold on 22nd. 

I have attached the sample dataset below with the desired outcome. 

https://docs.google.com/spreadsheets/d/1vaM-SyZSevNx5oUkvk3xwivwB-J_KuPZ/edit?usp=drive_link&ouid=10... 

[ryan_mayu]

@Anonymous 

the file has been deleted. pls try to upload.

[Anonymous]

I have updated the link. Thanks.

[ryan_mayu]

@Anonymous 

what's the expected output

e.g. there are three rows for soda of hyper marrket. What's the calculation logic?

[Anonymous]

The most recent one . You can change the date of the last one cause I have edited the original data as I cannot share the original data.

[ryan_mayu · Accepted solution]

@Anonymous 

here is a workaround for you

create a new column

_maxdate = 
 if(CALCULATE(max('Table'[Date_]),ALLEXCEPT('Table','Table'[Item Name],'Table'[Customer Name]))='Table'[Date_],"Y")

then you create a matrix and add a filter to select column is Y

pls see the attachment below

[Anonymous]

Thank you I appreciate it !

[ryan_mayu]

you are welcome

[Anonymous]

In my data the selling price is blank at the most recent transaction as the item is given free of charge. Is there a calculation that I can use to get LASTNONBLANKVALUE of the selling price?