cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sPowerBi Frequent Visitor
Frequent Visitor

Calculate NPI (non-productive Inventories in DAX

Hi,

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

DateProduct CodeQuantityPrice
1.01.2018B510
2.01.2018B610
3.01.2018B710
4.01.2018B810
5.01.2018B34511
6.01.2018B-2212
7.01.2018B-7612
8.01.2018B24312
9.01.2018B311
########B-6311
########B1212

I must do calculation, find result  such as bellow

DateProduct CodeQuantityPrice 
1.01.2018B510 
2.01.2018B610 
3.01.2018B710 
4.01.2018B810 
5.01.2018B34511210
6.01.2018B-22120
7.01.2018B-76120
8.01.2018B24312243
9.01.2018B3113
10.01.2018B-63110
11.01.2018B121212
  Total Stock468

 

Thanks

8 REPLIES 8
sPowerBi Frequent Visitor
Frequent Visitor

Calculate NPI ( non productive Inverntories)

Hi,

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

DateProduct CodeQuantityPrice
1.01.2018B510
2.01.2018B610
3.01.2018B710
4.01.2018B810
5.01.2018B34511
6.01.2018B-2212
7.01.2018B-7612
8.01.2018B24312
9.01.2018B311
########B-6311
########B1212

I must do calculation, find result  such as bellow

DateProduct CodeQuantityPrice 
1.01.2018B510 
2.01.2018B610 
3.01.2018B710 
4.01.2018B810 
5.01.2018B34511210
6.01.2018B-22120
7.01.2018B-76120
8.01.2018B24312243
9.01.2018B3113
10.01.2018B-63110
11.01.2018B121212
  Total Stock468

 

Thanks

v-huizhn-msft Super Contributor
Super Contributor

Re: Calculate NPI ( non productive Inverntories)

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.

1.PNG

Thanks,
Angelia

sPowerBi Frequent Visitor
Frequent Visitor

Re: Calculate NPI ( non productive Inverntories)

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 Super Contributor
Super Contributor

Re: Calculate NPI ( non productive Inverntories)

Hi @sPowerBi,

But how to get 210 when quantity 345>0?

Thanks,
Angelia

sPowerBi Frequent Visitor
Frequent Visitor

Re: Calculate NPI ( non productive Inverntories)

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 Super Contributor
Super Contributor

Re: Calculate NPI ( non productive Inverntories)

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 Frequent Visitor
Frequent Visitor

Re: Calculate NPI ( non productive Inverntories)

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

sPowerBi Frequent Visitor
Frequent Visitor

Re: Calculate NPI ( non productive Inverntories)

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

Capture.PNG

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 233 members 2,394 guests
Please welcome our newest community members: