Calculate NPI (non-productive Inventories in DAX

[sPowerBi]

Hi,

I want to calculate NPI ( non productive Inverntories) on power bi. I have stock movements table like this.

Date	Product Code	Quantity	Price
1.01.2018	B	5	10
2.01.2018	B	6	10
3.01.2018	B	7	10
4.01.2018	B	8	10
5.01.2018	B	345	11
6.01.2018	B	-22	12
7.01.2018	B	-76	12
8.01.2018	B	243	12
9.01.2018	B	3	11
########	B	-63	11
########	B	12	12

I must do calculation, find result  such as bellow

Date	Product Code	Quantity	Price
1.01.2018	B	5	10
2.01.2018	B	6	10
3.01.2018	B	7	10
4.01.2018	B	8	10
5.01.2018	B	345	11	210
6.01.2018	B	-22	12	0
7.01.2018	B	-76	12	0
8.01.2018	B	243	12	243
9.01.2018	B	3	11	3
10.01.2018	B	-63	11	0
11.01.2018	B	12	12	12
		Total Stock	468

Thanks

[sPowerBi]

Hi,

I want to calculate NPI ( non productive Inverntories) on power bi. I have stock movements table like this.

Date	Product Code	Quantity	Price
1.01.2018	B	5	10
2.01.2018	B	6	10
3.01.2018	B	7	10
4.01.2018	B	8	10
5.01.2018	B	345	11
6.01.2018	B	-22	12
7.01.2018	B	-76	12
8.01.2018	B	243	12
9.01.2018	B	3	11
########	B	-63	11
########	B	12	12

I must do calculation, find result  such as bellow

Date	Product Code	Quantity	Price
1.01.2018	B	5	10
2.01.2018	B	6	10
3.01.2018	B	7	10
4.01.2018	B	8	10
5.01.2018	B	345	11	210
6.01.2018	B	-22	12	0
7.01.2018	B	-76	12	0
8.01.2018	B	243	12	243
9.01.2018	B	3	11	3
10.01.2018	B	-63	11	0
11.01.2018	B	12	12	12
		Total Stock	468

Thanks

[v-huizhn-msft]

Hi @sPowerBi,

What's your calculation rules? How did you get the following column? Please list an example for more details, otherwise we can not help you.



Thanks,
Angelia

[sPowerBi]

Hi,

This  is movement table. And total stock quantity equal 468 on 11 January 2018. 

If the quantity is less than zero then the tansaction is the sales movement. If the quantity is greater than zero then the transaction is the buy movement. so I want to find which buying movements the stock consist of. I find out which buying movements by reducing Quantity of movement from total stock

I hope i can tell.

Thanks

[v-huizhn-msft]

Hi @sPowerBi,

But how to get 210 when quantity 345>0?

Thanks,
Angelia

[sPowerBi]

Hi,

Yes 345>0 but remaining stock is 210. I want calculate row by row like this;

if (  transaction  quantity  > 0 )

{

if ( remaining stock > 0 and remaining stock > quantity) 

       result : quantity

else 

     result : remaining stock

}

Thanks

[v-huizhn-msft]

Hi @sPowerBi,

In your thread, there is no [remaining stock] column, is it a measure? Or it's a column in resource table? Could you please share more details?

Thanks,
Angelia

[sPowerBi]

Hi @v-huizhn-msft,

I have found a solution as below in Sql. How to calculate in dax?

CREATE TABLE dbo.YourTable
([Date_] date, [Product] nvarchar(50), [Amount] DECIMAL(10,3), [Stock] DECIMAL(10,3))
;

INSERT INTO dbo.YourTable
([Date_], [Product], [Amount], [Stock])
VALUES
('2018-01-01', 'Urun1', 10,46 ),
('2018-01-02', 'Urun1', 20,46 ),
('2018-01-03', 'Urun1', 30,46 ),
('2018-01-04', 'Urun1', 1, 46 ),
('2018-01-05', 'Urun1', 3, 46 ),
('2018-01-06', 'Urun1', 5, 46 ),
('2018-01-01', 'Urun2', 2, 6),
('2018-01-02', 'Urun2', 4, 6)
;

SELECT *,
case when MIN(Stock) OVER(PARTITION BY Product ) > SUM(Amount) OVER(PARTITION BY Product ORDER BY Date_ desc)
Then Amount
when min(Stock) OVER(PARTITION BY Product)-(SUM(Amount) OVER(PARTITION BY Product ORDER BY Date_ desc)-Amount) <0 Then 0
Else min(Stock) OVER(PARTITION BY Product)-(SUM(Amount) OVER(PARTITION BY Product ORDER BY Date_ desc)-Amount) End RemainingStock
FROM dbo.YourTable
ORDER BY Product,Date_ ;

drop table YourTable

[sPowerBi]

Hi @v-huizhn-msft,

I just want to calculate "Remaining Stock" measure in Summarize function. After that i want to see sum of Quantity if trancation date the day before 90 days from today. 

Thanks