Forum Discussion
Calculate NPI (non-productive Inventories in DAX
Microsoft EmployeeHi sPowerBi,
But how to get 210 when quantity 345>0?
Thanks,
Angelia
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,
AngeliaHi 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
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