@stock_managment
2 Topics
Get values from the previous row - STOCK ON HAND
Hi, Sorry I've posted the same question before but maybe it was not well explained... I'm building a Power BI report to track stock and inventory levels for various items across different locations. My data source is the item ledger entry table from Navision stored in SQL Server. This table captures item movements for each date and location. I've imported the data using a SQL query: now I have a table where I have stock data for days where I had a movement. Example: Posting Date Item No Location Code Quantity 2024-05-10 ITEM-A LOC-1 10 2024-05-11 ITEM-B LOC-2 5 2024-05-12 ITEM-A LOC-1 -2 2024-05-14 ITEM-C LOC-2 15 Now I would like to have stock values for the previous days. This stock value is equal to the stock value from the previous day: Expected results (0 are not necessary): Posting Date Item No Location Code Quantity 2024-05-10 ITEM-A LOC-1 10 2024-05-11 ITEM-A LOC-1 10 2024-05-11 ITEM-B LOC-2 5 2024-05-12 ITEM-A LOC-1 -2 2024-05-12 ITEM-B LOC-2 5 2024-05-13 ITEM-A LOC-1 -2 2024-05-13 ITEM-B LOC-2 5 2024-05-14 ITEM-A LOC-1 -2 2024-05-14 ITEM-B LOC-2 5 2024-05-14 ITEM-C LOC-2 15 I've tried to do that with a SQL query and a cross join but I could not manage it. Is there a way to do that with a DAX function? Thanks!Solved1KViews0likes4CommentsSTOCK ON HAND TRACING & VALUE CALCULATION
Hi BI community, i've a data set approx. 450k lines from which i need to map the inventory history. I've used the following code for the measure and it displays accurate values. SOH = CALCULATE( SUM('STOCK MAPPING'[Trans QTY]), FILTER( ALL('STOCK MAPPING'[Order Date]), ('STOCK MAPPING'[Order Date])<=MAX('STOCK MAPPING'[Order Date]) ) ) However, for individual SKUs the stock values are only populated against the dates on which trasactions were registered. Secondly, i need to create a variable measure where stock on hand at each date between 2009 to present is multiplied with a standard unit cost (current one from a different table). Any suggestions please. Regards,Solved3.8KViews0likes3Comments