Forum Discussion
Building a time phased inventory
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.Okay, got it! Used the wrong operator and had unnecessary syntax. 😁
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...
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.
Hi Anonymous,
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
Okay, got it! Used the wrong operator and had unnecessary syntax. 😁