Forum Discussion
DAX Formula: Inventory QOH in reverse
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?
- Anonymous7 years agoNot applicable
Sorry, not entirely clear. Nothing is being inserted manually, just two calculated columns set up once and then that's all.