cancel
Showing results for
Did you mean:
Helper I

Finding last filtered non-zero value for item

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?

1 ACCEPTED SOLUTION
Community Support

``````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

Community Support

``````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

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.