Help Needed with DAX Measure for Calculating Projected On Hand Balance

[mwrs]

I'm working on a report where I need a DAX measure to calculate the projected on hand balance (OHB) of an item in a given warehouse in Power BI.

Here's the current  setup:

I have two main tables:

1. Planning Overview: This table contains all transactions for a given item in a warehouse, such as customer order outgoing quantity, distribution order ingoing quantity, etc. The quantity is either + or - (depending on if quantity is going in to stock or out of stock). Each transaction has a "Planning Date", indicating when it's planned to happen.

2. Inventory Fact Table: This table stores the inventory quantity of each item for warehouses at a given moment in time. I have restricted it to show me only the latest inventory quantity, so there should only be one row for a given "Item-Warehouse" combination.

The objective is to calculate the projected OHB by considering the latest inventory quantity as a starting point and then adjusting it based on transactions in chronological order. This should be a rolling measure.

For example, let's say today 1/1 Item A in Warehouse X has 100 qty in stock from the inventory fact table. Tomorrow 2/1, there's a customer order outgoing of 50 qty. So, the projected OHB on the 2/1 would be 100 - 50 = 50 qty. Then, the day after 3/1, there's a distribution order ingoing of 100 qty. So, the projected OHB on the 3/1 would be 50 + 100 = 150. And so on...

I need help with creating a DAX measure to achieve this rolling calculation of the projected OHB, taking into account the transactions' chronological order based on the planning date. Unfortunately, I am not able to share the data due to confidentiality.

I have tried several methodologies provided by ChatGPT without any success.

Any assistance or guidance on how to approach this in DAX measure would be greatly appreciated! Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI files.  Please review my solution there and adapt the measures to your dataset.

Hope this helps.

[lbendlin]

Most likely Power BI is the wrong tool for what you are trying to accomplish.

Anyway, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...