cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vpsoini
Helper I
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] :

 

DateItem numberQuantitySum of salesShipping country
01.11.2021001220FI
01.11.202100118NO
03.11.2021002315FI
04.11.202100312SE
05.11.202100120FI
05.11.202100216DE
06.11.202100322FI
07.11.20210021050FR
08.11.202100355FI

 

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

 

[Item]

 

 

Item numberLast unit price
0015
0023
0030.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 nounit price price FImargin % FIprice othersmargin % others
00151050%837,5%
0023540%540%
0030,5150%275%

 

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 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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])

Vlianlmsft_0-1636528947991.png

For meore details,please refer to the sample pbix

View solution in original post

1 REPLY 1
V-lianl-msft
Community Support
Community Support

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])

Vlianlmsft_0-1636528947991.png

For meore details,please refer to the sample pbix

Helpful resources

Announcements
Microsoft Build 768x460.png

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.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!