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

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

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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's avatar
      nmerl
      Frequent Visitor
      7 years ago
      table 1  
         
      Current inventory  
      apples6 
      bananas3 
         
         
      Table 2  
      Iventory Transactions
      3/27/2019apples-5
      3/26/2019apples3
      2/1/2019bananas1
      1/1/2019pears-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'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,