Calculate Historical On Hand Quantities

[Kevin_McKenna]

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!

[amitchandak]

@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]

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