Building a time phased inventory

[leletran]

Hello all,

I hope I'm not in over my head with this, but I'm trying to build a time phased inventory that will calculate available total quantity after an order is placed, with new orders subtracting from the available quantity. 

Looking at this screenshot, we have 22 orders for Item 110000-100. The total quantity we have available on hand is 1,610. With subsequent orders, it subtracts from the available quantity.

I have two tables I'm working with: the customer orders table and the item warehouse table (this table contains the available on-hand). I've created a conditional column where if the item and the warehouse matches between the customer orders table and the item warehouse table, then subtract Quantity Ordered from QtyOnHand:

Unfortunately, while this does give me the available quantity, it doesn't iterate to the next order with the same item and I'm not sure what to do next:

Ultimately, I'd for the result to look something like this: first order on 3/16 with 13 quantity ordered. So 1,610 - 13 = 1597 available on hand. Next order, 60 was ordered, so 1597 - 60 = 1537, and so on. 

 

I hope this is actually feasible. Any insight is greatly appreciated. Thank you!

Cheers,

Lele

 

 

 

[v-binbinyu-msft · Accepted solution]

Hi @leletran ,

This is because your "OrderDate" column have duplicated values, please try to add an Index column in PowerQuery pane, then modify your dax formula with "Index" column.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leletran]

After a pot of coffee and lots of pacing back and forth, I'm a bit closer. It's now using the updated available quantity on subsequent orders, but this works when the OrderDate is unique. When there are multiple orders with the same date, while the available quantity is correct, it's show the same quantity number for these non-unique dates.

Looking at orders placed on 7/5 as an example, 1470 is correct (1497 - 27 = 1470), but this is confusing.

I'd like for this to show 7/5 - 1 quantity ordered - remaining 1496
7/5 - 18 quantities ordered - remaining 1478

7/5 - 8 quantities ordered - remaining 1470

DAX formula: 

Figured out one problem, now stuck on another. Sigh...

[v-binbinyu-msft · Accepted solution]

Hi @leletran ,

This is because your "OrderDate" column have duplicated values, please try to add an Index column in PowerQuery pane, then modify your dax formula with "Index" column.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leletran]

Hi @v-binbinyu-msft,

Many thanks for your response! I was actually playing around with an Index column yesterday but could not get the query to work. I used the Index column option in Power Query (From 1 option if that matters?), and created a new measure and the numbers don't look right.

Time-Phased Quantity 4 is the new measure using Index column

DAX:

I revised again to use OrderDate and Index and received the same result as previous screenshot:

The OrderDate is important since it helps determine which transaction happened first, but I'm afraid I'm stuck trying to incorporate both OrderDate and Index in the formula. I'm sure I'm missing something incredibly silly. Thank you so much for your time and help! 

Cheers,

Lele

[leletran · Accepted solution]

Okay, got it! Used the wrong operator and had unnecessary syntax. 😁