Forum Discussion
DAX Formula: Inventory QOH in reverse
I would like to restart this ask in hopes that I will explain it better.
I have a perpetual inventory system where the program records all the previous transactions in a [Transaction] Table. It also records all of the item inventory information including the Current QOH in an [Item] table. Below is a quick sample of the 2 tables combined.
The calculation that I need is the QOH for the previous transactions (i've added them in using a formula: Last Known QOH - Current Transaction = QOH after Transaction occured.) IE: QOH for Apples is 10 and is stored in the [Item] Table.... 10 - 10 = 0.. so the previous QOH must have been 0 after the transaction occured. Moving in reverse... 0- (5) = +5...
I would like this value to be in a column (in the Transaction Table) so that I can graph it and run measures against it.. IE: How many days have elapsed since the QOH was 0?... etc...
There are about 1.5 million lines and 300k items. Would this make more sense to do in SQL prior to data pull? (not sure how'd I'd do that either) Or would M code be a better candidate?
Here is the example.... The last number in the QOH column (for each item) is the known QOH from the [Item] Table. The remainder QOH numbers before it are what I need to calculate.
| Category | Date/Time | Qty change | QOH |
| Apples | 11/13/18 1:00PM | -2 | 10 |
| Apples | 11/13/18 2:00PM | -3 | 7 |
| Apples | 11/13/18 3:00PM | -2 | 5 |
| Apples | 11/13/18 4:00PM | -5 | 0 |
| Apples | 11/13/18 5:00PM | 10 | 10 |
| Banannas | 11/12/18 6:00AM | -30 | 100 |
| Banannas | 11/12/18 8:00AM | -30 | 70 |
| Banannas | 11/13/18 1:00PM | -30 | 40 |
| Banannas | 11/13/18 2:00PM | 60 | 100 |
| Banannas | 11/13/18 3:00PM | -30 | 70 |
| Banannas | 11/13/18 4:00PM | -30 | 40 |
| Banannas | 11/13/18 5:00PM | -30 | 10 |
| Oranges | 11/12/18 6:00AM | -100 | 100 |
| Oranges | 11/12/18 8:00AM | 100 | 200 |
| Oranges | 11/13/18 1:00PM | -100 | 100 |
| Oranges | 11/13/18 2:00PM | 100 | 200 |
| Oranges | 11/13/18 3:00PM | -100 | 100 |
| Oranges | 11/13/18 4:00PM | 100 | 200 |
| Oranges | 11/13/18 5:00PM | -100 | 100 |
I've looked everywhere for this solution and I've tried several iterations of "Earlier" to get it to work but I'm still having trouble. The closest I can find that somewhat resembles what I want to do is:
by v-qiuyu-msft
I decided to take a shot... Using the data you provided I had to add a row for each category as a "beginning" balance. From there created an index using EARLIER (well, variables defined before the calculation which is the same as using EARLIER)
Index =
VAR CurrentCategory = Table1[Category]
VAR CurrentDate = Table1[Date/Time]
RETURN
CALCULATE(
COUNTROWS(
FILTER ( ALL ( Table1),
CurrentCategory = Table1[Category]
&& CurrentDate >= Table1[Date/Time]
)
)
)That tells me how many rows are less then or equal to the current row's data/time, and in the same category:
So then I can use that as a reference point of what came before the current row:
QOH =
VAR CurrentCat= Table1[Category]
VAR CurrentIndex = Table1[Index]
VAR CurrentQtyChg= Table1[Qty change]
RETURN
CALCULATE(
SUM ( Table1[Qty change]),
FILTER(
Table1,
CurrentCat = Table1[Category]
&& CurrentIndex >= Table1[Index]
)
)
Then the final output:
Maybe what you were looking for?
Thanks for the reply! I like the addition of an index and that may be the key. There are too many items and transactions to consider inserting a row manually.
What if we reverse the sort order so index:1 is the most recent date with the known QOH. Would this eliminate the need for a new row? I still don't know how that would look....Index Item Date/Time Qty Change QOH 1 Apples 11/13/2018 17:00 10 10 2 Apples 11/13/2018 16:00 -5 ? 10-10=0 3 Apples 11/13/2018 15:00 -2 ? 0-(5)=5 4 Apples 11/13/2018 14:00 -3 ? 5-(2)=7 5 Apples 11/13/2018 13:00 -2 ? 7-(3)=10 maybe...
QOH = IF index = 1, then use QOH from Item Table,
Calculate( QOH - Qty Change, Index-1)
?? Does that make sense?
Sorry, not entirely clear. Nothing is being inserted manually, just two calculated columns set up once and then that's all.
Hi, the logic makes sense, but I don't see where you are including the current inventory (last-day or on-hand inventory) to start the reversal calculation.
