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
Hello Zubair_Muhammad
Again thank you for your prompt replies and sorry for all the trouble.
I tried your file and it seems to be working to a certain extent.
I tried to put some more data into it after having skipped a day or 2 and when I use the skipped date on the slicer it does not show me the data for that day.
For Eg:
Date Size Type Quantity Price 03-01-18 10x10 A 40 20 Closing Stock 05-01-18 10x10 A 10 (-) Sale So if I select 4th Jan on the slicer, everything goes blank and there is no closing stock for it.
I am trying to generate a real world scenario, where there will be holidays, so there will be no outward or inward on some dates.I have created a Calendar table, as you must have seen in the file I sent.
Also is there some way that I can get the closing stock for the last date of that month, if I put the month-Year on the slicer.
If you could please find a solution to these.
I am also updating the PBI and excel files on my OneDrive, if you it reduces your work and so that you know what I am doing wrong.
https://1drv.ms/f/s!Ap0qSKP-4qpThCGX0VuaSk-I9cxx
Again sorry for all the trouble and thank you for being so helpful.
Vishesh Jain