historical Inventory Over Time

[nmerl]

First time posting, so go easy

I have two tables, one which shows my current inventory at any given time,

I have a separate inventory transaction table.

i want to create a running total of my inventory by reversing all my inventory transactions from today, starting with the current inventoy on hand number.

not sure how to create a running total starting with the current on hand?

thanks for any help.

[v-yuta-msft]

@nmerl ,

About how to write DAX on running total, you may refer to this tutorial: https://www.daxpatterns.com/cumulative-total/.

In addtion, if you still can't solve this issue, please share some sample data and expected result.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nmerl]

table 1
Current inventory
apples	6
bananas	3
Table 2
Iventory Transactions
3/27/2019	apples	-5
3/26/2019	apples	3
2/1/2019	bananas	1
1/1/2019	pears	-5

Lets say these are my two tables, i am able to get the running change of each item from the transaction table, but i want to add that starting number for my current inventory and then work backwords,

so i can say today i have 6 apples, i sold 5 yesterday, so i must have had 11 apples in house. etc.

as a second part, which might add more complications, today i have no pears in stock, i sold my last 5, so on Jan 1, i must have had 5

Any ideas how i can link the 2 tables, and make sure that i am doing my math in reverse date order 

[nmerl]

i've been plowing ahead, and thought i was making great progess, until i unfiltred my data, and allowed all items into my query, previously, i had been working with only one item, and thought i could build the logic and all would be good.

i combined my transaction table, and inventory on hand table, i was set a transaction date to the future, for my Inventory on hand, then calculated the reverse of my transactions, so i can go back, and build up my inventory.

I added a column for running total, which works perfectly, with only one item, but i need it to make a new running total for each item.

this is what i have in my running total column.

RUNNING TOTAL = CALCULATE(SUM(IV00102[Column]),ALL(IV00102),IV00102[DOCDATE]>=EARLIER(IV00102[DOCDATE]))

i assume i need to make this a measure, but not sure wehre i am going wrong, that i can not gget it to work.

any other ideas to help me out would be appreciated.

when i use the quick measure i just get the sum of the transactions and not the running balance,

[nmerl · Accepted solution]

I think i solved my issue by making a column like this

column4 = CALCULATE(

SUM(IV00102[Column]),

FILTER(

ALL(IV00102),IV00102[ITEMNMBR]=EARLIER(IV00102[ITEMNMBR])

&& (IV00102[DOCDATE]>=EARLIER(IV00102[DOCDATE])))

)

working on validating my data, but i think it seemed to have worked!!