How to calculate daily stock for all items and locations in Power BI using item ledger entries
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 the following SQL query: WITH day_quantity AS ( SELECT il.[Posting Date] as posting_date, il.[Item No_] as item_no, il.[Location Code] as location_code, SUM(il.[Quantity]) as quantity FROM [COG$Item Ledger Entry] AS il GROUP BY il.[Posting Date], il.[Item No_], il.[Location Code] ), SELECT posting_date, item_no, location_code, SUM(quantity) OVER (PARTITION BY item_no, location_code ORDER BY posting_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS stock FROM day_quantity This query calculates the stock quantity for each item and location on days with movements. However, it doesn't provide a complete picture of the actual stock for days without transactions. My questions are: - Is importing a view from SQL Server with a structure like "date, item, location, quantity" (one line per unique combination) a suitable approach? While I don't need a record for items with zero quantity, I'm concerned about the potential data volume. It will be the best option for me because I see how to calculate variations but I didn't manage to write the right SQL query. - Can I use DAX measures to calculate the daily stock for all items and locations, even on days without transactions? I'm relatively new to DAX and would appreciate guidance on creating such a measure.