Forum Discussion
Stocke Movements
Hello,
I am having trouble working out a calculated measure for stock levels. I have a fairly standard table of stock movements, each record has a movement amount (positive if material is added and negative if some has been used). Each record has a material type ID and an SKU ID as well as a date of movemement.
Date SKU ProductTypeID Amount
| 01/01/2023 | 100000 | 100000 | 2500 |
| 01/01/2023 | 100001 | 110123 | 3000 |
| 02/01/2023 | 100000 | 100000 | -500 |
| 02/01/2023 | 100001 | 110123 | -125 |
| 03/01/2023 | 100000 | 100000 | -100 |
| 03/01/2023 | 100001 | 110123 | -50 |
| 04/01/2023 | 100000 | 100000 | -100 |
| 04/01/2023 | 100001 | 110123 | 250 |
| 05/01/2023 | 100000 | 100000 | -250 |
| 05/01/2023 | 100001 | 110123 | -250 |
| 16/01/2023 | 100000 | 100000 | -1000 |
| 16/01/2023 | 100001 | 110123 | -1250 |
| 17/01/2023 | 100000 | 100000 | -50 |
| 17/01/2023 | 100001 | 110123 | -100 |
What I need to be able to do is -
1) show a table, probably per month but may need to be per year or even per day showing the total amount of stock, filtered by SKU or ProductTypeID
2) produce a list of all SKU's with a positive balance on a given day - ie for the 10/01/2023 I want to see that SKU 100000 had 1550 meters left and 100001 had 2825 meters left.
My movements table has approx 500,000 rows in it. I have tried -
You will need to use a disconnected calendar table to feed your slicer, and measures to calculate the stock level for each product on the date specified by the slicer.
1 Reply
You will need to use a disconnected calendar table to feed your slicer, and measures to calculate the stock level for each product on the date specified by the slicer.