Calculate the Inventory OnHand Qty (FIFO) is remaining from which purchase transaction for further analysis

DAX DAX Calculations
1

Dears,
I’ve Purchases transactions for Items, and also sales transactions.

The requirements: assumes that the OnHand Qty as of Today is 748 Pcs,
I want a way to know from which purchase transactions these qty came from, assuming that FIFO is the inventory method (First In First Out)

In Excel, I used Offset and some other formulas

The result should be like that:

image
image735×499 25.4 KB

And the clean version keeps only the lines with Qty OnHand like that:

image
image724×324 17.3 KB

Then, I will create Aging for these Qty as I need to know how long this items is idle in the warehouse.

I want to achieve the same with DAX, I am attaching the Excel file.

I wish if someone can help me with this data and not to refer to other forum posts, as the Aging pattern will not work correctly until we do the same in the screenshot

OnHand Qty FIFO.xlsx (74.6 KB)

2

Bumping this post for more visibility from our experts and users.

1 Like
3

Hi @mahmoud.algindy

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the expert’s and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

4

Hi,
I wonder if it left unsolved, why it’s marked as solved?