Forum Discussion
Stock On Hand Measure From Stock-In Table and Sales Table
I need to create a measure that caulculates the stock on hand at any given day for an item based on a sales table:
| ITEM | SALE_AMOUNT | SALE_DATE |
| A | 1000 | 2022-05-23 |
| B | 500 | 2022-05-23 |
| C | 2000 | 2022-05-22 |
| A | 3000 | 2022-05-22 |
| C | 200 | 2022-05-21 |
| A | 100 | 2022-05-20 |
And a Stock-In table:
| ITEM | QUANTITY | STOCKED_DATE |
| A | 5000 | 2022-05-19 |
| B | 4500 | 2022-05-19 |
| C | 5000 | 2022-05-19 |
| A | 5000 | 2022-05-22 |
I would like to be able to plot stock on hand against date and filter by ITEM.
I have looked at other posts and tried various methods including creating a date table but can't seem to get it right.
Appreciate any help.
3 Replies
Hi
Try This
Stock situation =
VAR stockdatevalue =
SELECTEDVALUE ( 'Date'[Date] )
VAR stockqty =
CALCULATE (
SUM ( Stock[Stock] ),
ALL ( 'Date'[Date] ),
Stock[Date] <= stockdatevalue
)
VAR salesqty =
CALCULATE (
SUM ( 'Stock SALES'[sales] ),
ALL ( 'Date'[Date] ),
'Stock SALES'[Date] <= stockdatevalue
)
RETURN
stockqty - salesqtyAnonymous , what would stock on 20th May. blank or 14500. Create a date table join with both the tables on date
For stock try a meausre like
calculate(lastnonblank(Date[Date], sum(Stock[Quanity]), filter(all(date), Date[date]<= max(Date[date])))
Thanks for the help amitchandak, ideally the stock on 20th May would be 14400 (total stock less 100 of item A sold). I will try your suggestions later today.
