Finding last filtered non-zero value for item

[vpsoini]

Hi.

I'm calculating margin % per item from item legder and for that I'm trying to find last non-zero value per item for domestic and foreing shipments. Same item number natularry exists in the ledger multiple times over time, but I'm interested in last (non-zero) value separately for domestic (my case country code: FI) and foreing ( <> FI) cases. So I'm looking for 2 non-zero values per item.  And there's about 50k items in total.

Sample table [Item legder] :

Date	Item number	Quantity	Sum of sales	Shipping country
01.11.2021	001	2	20	FI
01.11.2021	001	1	8	NO
03.11.2021	002	3	15	FI
04.11.2021	003	1	2	SE
05.11.2021	001	2	0	FI
05.11.2021	002	1	6	DE
06.11.2021	003	2	2	FI
07.11.2021	002	10	50	FR
08.11.2021	003	5	5	FI

And then I have another table for cost = item unit price

[Item]

Item number	Last unit price
001	5
002	3
003	0.5

I would need to find the last prices per item and calculate margin % for them. And this separately for domestic and foreing shipments, so the output I'm looking for is

Item no	unit price	price FI	margin % FI	price others	margin % others
001	5	10	50%	8	37,5%
002	3	5	40%	5	40%
003	0,5	1	50%	2	75%

What is the DAX-way of finding those last non-zero values for FI / non-FI per item for calculating the margins?

Thanks a lot in advance for your help 

[V-lianl-msft · Accepted solution]

Please create measure like below:

price = 
var no_0_max_date = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Item number]),'Table'[Sum of sales]>0&&'Table'[Shipping country]="FI"))
return CALCULATE(DIVIDE(SUM('Table'[Sum of sales]),SUM('Table'[Quantity])),FILTER('Table',[Shipping country]="FI"&&'Table'[Date]=no_0_max_date))

margin % = DIVIDE([price]-MAX('Table (2)'[Last unit price]),[price])

For meore details,please refer to the sample pbix