Calculated Stock on Hand -at any given point of time

[Anonymous]

Hi, 

I want to show stock on hand qty at any point of time. 
I have 2 tables 
1.StockBal
2.StockTransaction

StockBal table stores  SOH of the item and for a site at the end of every month. So for a item and a for site it will 1 entry per month. StockBal - has Feb data only.

Stock Transaction table stores, daily transactions for a item and site. 

 In order for me to calculated the SOH for Jan. I subtract StockBal Qty with transacted qty to showing historical data. for March I add Stockbal Qty with Transacted qty to get SOH.

StkUnitQty is daily transcated qty

Measure to calculated Culmulated StkUnitQty

Cumulative StkUnitQty =

Var DMnth = ENDOFMONTH(StockTransaction[AccountingDate])

Var HStockdate =ENDOFMONTH(StockBal[StockDate])

Var CQTYafterSTBaldate =

CALCULATE(SUM(StockTransaction[StkUnitQty]),FILTER(StockTransaction

    ,StockTransaction[ItemRef]=EARLIER(StockTransaction[ItemRef])

            && StockTransaction[StorageSite]=EARLIER(StockTransaction[StorageSite])

                && StockTransaction[Unit]=EARLIER(StockTransaction[Unit])

                    &&StockTransaction[AccountingDate]>StockTransaction[DateFTStockBalance]

                        &&StockTransaction[AccountingDate]<=EARLIER(StockTransaction[AccountingDate])))

Var CQTYbeforeSTBaldate =

CALCULATE(SUM(StockTransaction[StkUnitQty]),FILTER(StockTransaction

    ,StockTransaction[ItemRef]=EARLIER(StockTransaction[ItemRef])

            && StockTransaction[StorageSite]=EARLIER(StockTransaction[StorageSite])

                && StockTransaction[Unit]=EARLIER(StockTransaction[Unit])

                    &&StockTransaction[AccountingDate]<=StockTransaction[DateFTStockBalance]

                        &&StockTransaction[AccountingDate]>=EARLIER(StockTransaction[AccountingDate])))

Return IF (StockTransaction[AccountingDate] >StockTransaction[DateFTStockBalance], CQTYafterSTBaldate,CQTYbeforeSTBaldate)

Measure to calculated SOH in Stock Transacted table is 
SOH=

If(StockTransaction[AccountingDate]<=StockTransaction[DateFTStockBalance],

StockTransaction[FTStockBalance]-StockTransaction[Cumulative StkUnitQty]

,IF(StockTransaction[AccountingDate]>StockTransaction[DateFTStockBalance],

StockTransaction[FTStockBalance]+StockTransaction[Cumulative StkUnitQty]))

e.g StockBal for item 14L is 

and Stock Transaction for same is 

When I add this measure then it adds the SOH qty,

Requried result should be 

It should show the next SOH qty rather then same adding the next qty in the previous qty. 

Measure to show final StockHand Qty
Stock on Hand = CALCULATE(SUM(StockTransaction[SOH])

,FILTER(ALL(Dates),Dates[Date]<=MAX(Dates[Date])))

I am sharing the pbix file
https://drive.google.com/file/d/1Z4I3hQgNFCxaIIQJByjkz4S6uj5PmJn8/view?usp=sharing

Thanks.

[lbendlin]

SOH is a caclulated column, not a measure.  it can be simplified:

SOH = If(StockTransaction[AccountingDate]<=StockTransaction[DateFTStockBalance], 
StockTransaction[FTStockBalance]-StockTransaction[Cumulative StkUnitQty]
,StockTransaction[FTStockBalance]+StockTransaction[Cumulative StkUnitQty])

Same for Cumulative StkUnitQty - it's a calculated column, not a measure.  And that's probably ok since these values are not influenced by user filters.

I have to admit I don't fully understand why you sometimes add and sometimes subtract.  Your calculated columns can be simplified - have a look at the documentation for CALCULATE(), and also consider replacing EARLIER with variables.