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.
