Calculate stock in hand from initial date

[User_790790]

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

[ThxAlot · Accepted solution]

[sanalytics · Accepted solution]

@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

[pcoley · Accepted solution]

@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😁.

[User_790790]

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=10407... 

[sanalytics · Accepted solution]

@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

[ThxAlot · Accepted solution]