topic Re: Calculate stock in hand from initial date in DAX Commands and Tips

Calculate stock in hand from initial date

User_790790 — Fri, 27 Dec 2024 17:05:58 GMT

I have the following table below :-

The remaining stock in hand from the end of the month (30th sep) is 30.

I need to calculate stock in hand like this

01-10-2024 Stock in hand = Stock in hand end of month + Quantity Issued on 1st - QTY Sold
= 30 + 5 - 2
= 33

Then the remaining stock from the previous day to be added with qty issued like the below
02-10-2024 Stock In Hand = 33 + 6 - 7 = 32

How can I replicate this in Power bi?

Date	Item Name	Salesman	Quantity Issued	Qty Sold	Stock In Hand
01-10-24	TV	John	5	2	33
02-10-24	TV	John	6	7	32
03-10-24	TV	John	13	6	39
04-10-24	TV	John	14	3	50
05-10-24	TV	John	5	6	49
06-10-24	TV	John	5	7	47
07-10-24	TV	John	3	8	42
08-10-24	TV	John	10	9	43
09-10-24	TV	John	6	2	47
10-10-24	TV	John	9	6	50
11-10-24	TV	John	6	3	53
12-10-24	TV	John	6	10	49

Re: Calculate stock in hand from initial date

ThxAlot — Sat, 28 Dec 2024 05:42:10 GMT

Re: Calculate stock in hand from initial date

sanalytics — Sat, 28 Dec 2024 10:01:20 GMT

User_790790
In case you want to achieve this result as measure instead of calculated column.
Below are the code

EOD SIH = 30 //It emans Sep's Stock in Hand

StockInhand = VAR _QtyIssueRT = CALCULATE( SUM( 'Table'[Quantity Issued] ), FILTER( ALL( 'Table'[Date] ), 'Table'[Date] <= MAX( 'Table'[Date] ) ) ) VAR _QtySoldRT = CALCULATE( SUM( 'Table'[Qty Sold] ), FILTER( ALL( 'Table'[Date] ), 'Table'[Date] <= MAX( 'Table'[Date] ) ) ) VAR _Result = [EOD SIH] + _QtyIssueRT - _QtySoldRT RETURN _Result

Below screenshot

Let me know if you need help for end of the mnth stock in hand.

Hope it helps.

Regards,

sanalytics

Re: Calculate stock in hand from initial date

pcoley — Sat, 28 Dec 2024 20:08:46 GMT

User_790790
Do you need a calculated column or a measure?
If it is a measure please be more precise about the model (tables and relationships available) and the existing context in the report. actually that measure correspond to a running total with the measure

stock= SUM ('Table'[Quantity Issued]) - SUM ('Table'[Qty Sold])

if all the columns are placed in the same "Table" then this measure could work:

Measure Stock in hand= CALCULATE ( SUM ( 'Table'[Quantity Issued] ) - SUM ( 'Table'[Qty Sold] ), FILTER ( ALL('Table'), 'Table'[Date] <= MAX('Table'[Date] ) ) )

if you need a calculated column please use this CalcColumn Dax formula:
--> changing the "table" with your actual table name.

😁Column = CALCULATE ( SUM ( 'Table'[Quantity Issued] ) - SUM ( 'Table'[Qty Sold] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ) )

I hope this helps, if so please accept as a solution. Kudos are welcome😁.

Re: Calculate stock in hand from initial date

User_790790 — Fri, 10 Jan 2025 17:48:59 GMT

Thanks, i'll check it out. Sorry for the lack of clarity. I have three tables one is stock in hand from prev month, quantity issued, sales data. I would like to get the result in a new table if it's possible.

https://docs.google.com/spreadsheets/d/1mqJBUTHIwDU380uukvLYloHRgZsnS6Mz/edit?usp=sharing&ouid=104076749508283104516&rtpof=true&sd=true