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