Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |