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
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] )
)
)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 ) )