Forum Discussion
Calculate stock in hand from initial date
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 |
User_790790
In case you want to achieve this result as measure instead of calculated column.
Below are the codeEOD SIH = 30 //It emans Sep's Stock in HandStockInhand = 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 _ResultBelow screenshot
Let me know if you need help for end of the mnth stock in hand.
Hope it helps.
Regards,
sanalytics
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 measurestock= 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😁.
4 Replies
- ThxAlot
Super User
sanalyticsUser_790790
In case you want to achieve this result as measure instead of calculated column.
Below are the codeEOD SIH = 30 //It emans Sep's Stock in HandStockInhand = 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 _ResultBelow screenshot
Let me know if you need help for end of the mnth stock in hand.
Hope it helps.
Regards,
sanalytics
- pcoley
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 measurestock= 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😁.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
