Forum Discussion

Kevin_McKenna's avatar
Kevin_McKenna
New Member
5 years ago

Calculate Historical On Hand Quantities

Hello!  I am realtively new to Power BI, so I am hoping I can get some help calculating historical on hands for parts in my warehouse.  Here are Table/columns I think I need to use.  All may not be necessary.

 

'Calendar'[Date]

'Parts'[PartNum]

'Inventory'[OnHandQty] - Table only shows my inventory levels as of today

'Sales'[SalesUnits] - Table shows sales by date for each part out of my warehouse.  Sales are not summarized; could be multiple rows of sales for a part within the table.

'Containers'[ReceivedQty] - Table shows receipts by date for each part into my warehouse

 

Here's a hypothetical.  Part 12345.  Inventory on 3/11/2021 as per 'Inventory'[OnHandQty] is 1000.

'Sales'[SalesUnits] shows sales of 200 on 3/10/2021, 100 on 3/9/2021.

'Containers'[ReceivedQty] shows receipts of 100 on 3/10/2021, 1200 on 3/9/2021

 

I know the math.  My on hand at the beginning of 3/10/2021 is:

(1000 OH 3/11) + (200 sales 3/10) - (100 receipts 3/10) = 1100 units

 

On hand at the beginning of 3/9/2021:

(1100 calc OH 3/10) + (100 sales 3/9) -(1200 receipts 3/9) = 0 units

 

What is the correct DAX language to do that math?

 

Many thanks in advance!

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Kevin_McKenna , With help from a common date table, joining with Sales and Containers

     

    Quantity on hand = [Todays stock] + CALCULATE(Sum('Sales'[SalesUnits]),filter(allselected(Date),Date[Date] >=min(Date[Date]))) -CALCULATE(sum('Containers'[ReceivedQty] ),filter(allselected(Date),Date[Date] >=Min(Date[Date])))

    • Kevin_McKenna's avatar
      Kevin_McKenna
      New Member
      5 years ago

      amitchandak - Apologies for the delayed response

       

      I wrote the following measure based on your suggestion above:

       

      Historical On Hand = 

      SUM('Inventory'[OnHandQty]) + 

      CALCULATE(SUM('Sales'[SalesUnits]),filter(allselected('Calendar'),'Calendar'[Date]>=min('Calendar'[Date]))) -

      CALCULATE(SUM('Containers'[ReceivedQty]),filter(allselected('Calendar'),'Calendar'[Date]>=min('Calendar'[Date])))

       

      Here is partial output:

       

      Date               Historical On Hand

      3/24/2021        9,476,844

      3/23/2021        -65978

      3/22/2021        -322746

       

      The calculation of sales and receipts looks to be correct (we received 65,978 more units than we sold on 3/23).  However, it looks like the 3/24 OH is not being used to calculate the 3/23 OH (0 + 45,112 sales - 111,120 receipts = -65,978 OH) which makes the 3/22 OH calculation incorrect (-65,978 OH + 56,550 sales - 313,318 receipts = -322,746 OH), on and on. 

       

      Output should be 3/23/2021 OH 9,410,836 (9,476,844 + 45,112 - 111,120), 3/22 OH 9,154,068 (9,410,836 + 56,550 - 313,318), etc.

       


      Thank you for trying to help me.  I am going to try and play around with the measure but any further help you could give would be greatly appreciated!