Forum Discussion
Anonymous
9 years agoNot applicable
DAX Formula: Inventory QOH in reverse
Disclaimer: I just started working with DAX and I've read and watched a ton of content but I'm still getting hung up on very simple problems. I learn faster if I can see the solutions to the problem...
Anonymous
7 years agoNot applicable
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?
leandroab1
Advocate II
2 years agoHi, 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.