Forum Discussion

nmerl's avatar
nmerl
Frequent Visitor
7 years ago
Solved

historical Inventory Over Time

First time posting, so go easy :smileyhappy:   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 runni...
Need Help
  • nmerl's avatar
    nmerl
    7 years ago

    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!!
nmerl's avatar
nmerl
Frequent Visitor
7 years ago

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's avatar
nmerl
Frequent Visitor
7 years ago

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!!