Forum Discussion

vpsoini's avatar
vpsoini
Icon for Helper I rankHelper I
4 years ago
Solved

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 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    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

1 Reply

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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