I'm stuck building an inventory report in power bi and am looking for some help. A bit of context: I'm using data from our ERP system Dynamics Nav.

The following tables have been added to my power bi model:

1. a general date table
2. item ledger entries table (table with all item transactions)
3. dimension product table (table with all products)
4. registered inspection change table: all changes to the inspection status of a certain lot number have been logged in this table. It contains columns such as 'lot number', 'product number', 'date of change', 'time of change', 'current inspection status' and 'new inspection status'. We have multiple statusses: 'on hold', 'blocked', 'on hand'. The inspection status of a product (or better the specific lot number of a product) can be changed multiple times over time, for instance from 'on hold' to 'blocked', and from 'blocked to 'on hand'. So there can be multiple lines per lot number in this table.

I have created the following relationships:

• Item ledger entries (posting date) is linked to the dimension time table (date)
• Item ledger entries (product number) is linked to the dimension product table (product number)
• Item ledger entries (lot number) to registered inspection change (lot number)

When I then create a table matrix visual with my product number (dimension product table) in rows and my quantity from my item ledger entries in the values, I get the correct inventory stock level per product. Also when I put a date slicer on the visual (e.g. to show me stock up to 31st december 2024), it shows the correct stock level per product at that specific date.

However, the complexity comes in when I want to create a visual that shows the stock level per status ('on hold', 'blocked', 'on hand'). I do not succeed in creating a report which shows the correct stock level per status, on a specific date. Suppose a lot number initially had the status 'on hold' and was changed on the 21st of january 2025 to 'blocked' --> when i filter my inventory report on '19th of january' it should count the stock of that lot number in the 'on hold' category, however when I would filter the inventory report on the 22nd of january , it should count the stock of that lot number in the 'blocked' category.

How can I make sure I can use the inspection status in the rows of my table matrix and the quantity of the item ledger entries in my values? So that it shows the correct stock at a given point in time per inspection status (the inspection status it had at that time)?

Any help would be very much appreciated