Forum Discussion
Closing Stock Calculation
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
To get the Missing Dates, we can create another Table
Final Table = CROSSJOIN ( ALL ( CombinedTable[Date] ), ALL ( Inward[Size], Inward[Type] ) )
Then Add a calculated Column to it as follows
Closing_Stock =
VAR maxdate =
CALCULATE (
MAX ( CombinedTable[Date] ),
FILTER (
CombinedTable,
CombinedTable[Date] <= 'Final Table'[Date]
&& CombinedTable[Size] = 'Final Table'[Size]
&& CombinedTable[Type] = 'Final Table'[Type]
)
)
RETURN
CALCULATE (
VALUES ( CombinedTable[Closing Stock] ),
FILTER (
CombinedTable,
CombinedTable[Size] = 'Final Table'[Size]
&& CombinedTable[Type] = 'Final Table'[Type]
&& CombinedTable[Date] = maxdate
)
)Hello Zubair_Muhammad
Thank you for your prompt replies.
After having tried to type your code in my file and getting erros, I have changed the names of all my tables and columns to match yours and have literally copy pasted your formula in my file and it is still giving me errors.
For some reason in the 'Final Table' it is not taking the Date, Size and Type columns in the formula. I have checked your file as well and it has the exact same columns and names and everything, but for some reason your file works and mine doesn't.
So if you could please have a look at my file and if you could point out, where am I going wrong here.
https://1drv.ms/f/s!Ap0qSKP-4qpThCGX0VuaSk-I9cxx
Here is the link to my Power BI file and the Excel file.
Also I have added another price column in the Inward file which I want to take into consideration, while calculating the closing stock.
For eg:
| Date | Size | Type | Quantity | Price | |
| 02-01-18 | 20x20 | B | 20 | 50 | (+) Previous Date Closing Stock |
| 03-01-18 | 20x20 | B | 50 | 60 | (+) Purchase |
| 03-01-18 | 20x20 | B | 10 | 50 | (-) Sale |
| 03-01-18 | 20x20 | B | 10 | 50 | (=) Current Closing Stock |
| 03-01-18 | 20x20 | B | 50 | 60 |
So since all the quantity of the previous closing stock has not been used and there was a new purchase, there is a closing stock on 3rd Jan of B, 20x20, 10@Price 50 and 50@Price 60.
It would really help me out if you could help me out with this.
Again thank you so much for all your help.
Vishesh Jain