cancel
Showing results for
Did you mean:
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.

 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

8 REPLIES 8
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.

 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

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.

Thanks,
Angelia

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

Super Contributor

## Re: Calculate NPI ( non productive Inverntories)

Hi @sPowerBi,

But how to get 210 when quantity 345>0?

Thanks,
Angelia

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

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

Frequent Visitor

## Re: Calculate NPI ( non productive Inverntories)

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

Frequent Visitor

## Re: Calculate NPI ( non productive Inverntories)

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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 233 members 2,394 guests
Recent signins: