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
Thank you again for helping me out with the problem.
I was also working on the same lines are your proposed solution(which is quite similar to the last time you helped me out), but I created that table as a query instead.
Anyways, your solution almost works but there is a flaw in it.
The solution is not showing me Closing Stock of a particular Size and Type, if there is no outward for it on a particular date.
Hence when I use a date slicer on it, it will not show me data in the Closing stock for that particular date.
For eg:
Your solution is missing the following from the Closing Stock table:
For 2nd Jan - A, 30x30, 30
For 3rd Jan - B, 20x20, 20
If you could please somehow work this out, it will be great.
Meanwhile I am also working on it and if I come up with a solution, I will let you know.
Thank you again for 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