Forum Discussion
Closing Stock Calculation
Hello,
I have the following 2 tables. First one is Inward and second one is Outward.
| Inward | |||
| Date | Size | Type | Quantity |
| 01-01-18 | 10x10 | A | 100 |
| 01-01-18 | 20x20 | B | 100 |
| 01-01-18 | 30x30 | A | 100 |
| 02-01-18 | 10X10 | B | 200 |
| 03-01-18 | 10x10 | A | 50 |
| 03-01-18 | 30x30 | A | 50 |
| Outward | |||
| Date | Size | Type | Quantity |
| 01-01-18 | 10x10 | A | 20 |
| 01-01-18 | 20x20 | B | 30 |
| 01-01-18 | 30x30 | A | 70 |
| 02-01-18 | 10x10 | A | 50 |
| 02-01-18 | 10x10 | A | 20 |
| 02-01-18 | 20x20 | B | 50 |
| 03-01-18 | 10x10 | A | 20 |
| 03-01-18 | 10x10 | B | 50 |
| 03-01-18 | 10x10 | B | 30 |
| 03-01-18 | 30x30 | A | 70 |
I am trying to get this third table as a result, which is my Closing stock for each date by size and type both.
| Closing | |||
| Date | Size | Type | Quantity |
| 01-01-18 | 10x10 | A | 80 |
| 01-01-18 | 20x20 | B | 70 |
| 01-01-18 | 30x30 | A | 30 |
| 02-01-18 | 10x10 | A | 10 |
| 02-01-18 | 20x20 | B | 20 |
| 02-01-18 | 30x30 | A | 30 |
| 02-01-18 | 10x10 | B | 200 |
| 03-01-18 | 10x10 | A | 40 |
| 03-01-18 | 10x10 | B | 120 |
| 03-01-18 | 30x30 | A | 10 |
| 03-01-18 | 20x20 | B | 20 |
I am calculating my closing stock by FIFO method.
For Example to calculate closing stock for 03-01-2018:
| Date | Size | Type | Quantity | |
| 02-01-18 | 10x10 | A | 10 | (+) Previous Date Closing Stock |
| 03-01-18 | 10x10 | A | 50 | (+) Purchase |
| 03-01-18 | 10x10 | A | 20 | (-) Sale |
| 03-01-18 | 10x10 | A | 40 | (=) Current Closing Stock |
I want to create a table that will take into consideration the size and type and then give me a closing stock as a new table.
I dont mind if it works as a measure, or a query.
If anyone can help me out with this, it would be great.
If you need any other information or if you need any further clarification on my problem, then please let me know.
Thank you,
Vishesh Jain
Thanks again for taking the time to help me out.
I have updated my file to get the closing stock after taking into consideration the last date for every month, all thanks to your help. I have created a new measure 'On Hand Quantity' in the 'Final Table'.
Here is the link:
https://1drv.ms/f/s!Ap0qSKP-4qpThCGX0VuaSk-I9cxx
Now I am trying to get the prices in my closing stock table, so that all my closing stock can be bifurcated.
I would really like to know from where have you learned how to code DAX cause you are so quick with your solutions and they work!
I have been spending hours an hours and not getting any results. If you could please tell me if there is any book that I can use to learn how to code in DAX.
Again a huge thanks for all your help.
Vishesh Jain
18 Replies
Try this
First Create a CombinedTable. From the Modelling Tab>>NEW TABLE
CombinedTable = UNION ( SUMMARIZE ( Inward, Inward[Date], Inward[Size], Inward[Type], "Quantity", SUM ( Inward[Quantity] ) ), SUMMARIZE ( Outward, Outward[Date], Outward[Size], OUTward[Type], "Quantity", - SUM ( oUTward[Quantity] ) ) )Then add this MEASURE in this new Table
Closing_Stock = CALCULATE ( SUM ( CombinedTable[Quantity] ), FILTER ( ALLEXCEPT ( CombinedTable, CombinedTable[Size], CombinedTable[Type] ), CombinedTable[Date] <= SELECTEDVALUE ( CombinedTable[Date] ) ) )