Closing Stock Calculation

[mail2vjj]

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

[mail2vjj · Accepted solution]

@Zubair_Muhammad

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

[Zubair_Muhammad]

@mail2vjj

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

[Zubair_Muhammad]

@mail2vjj

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

[mail2vjj]

Another thing I am seeing in my table is that, if I take the Size and Type from their seperate respective tables, the measure starts giving wrong results.

It is just adding the Closing Stock up for that Date and showing it in every single row, regardless or Type and Size.

Again this will be a problem when I use the Type and Size in the slicer.

I have created relationships between the new Combined Table and the Type and Size tables, but the values itself are wrong.

It would be great if you can help me fix this.

Thank you,

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

[mail2vjj]

Hi @Zubair_Muhammad

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

[Zubair_Muhammad]

@mail2vjj

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

[mail2vjj]

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

[Zubair_Muhammad]

@mail2vjj

Please check your file here

Closing Stock Calculated Column  was missing in the Final Table

[mail2vjj]

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

[Zubair_Muhammad]

@mail2vjj

I will look into it

[mail2vjj]

@Zubair_Muhammad

Thank you for your help.

As you might have figured out by now, I am quite new to Power BI and the coding behind it.

I am trying to implement BI in my organization and not many people here are familiar with it, that is why I have multiple threads running at the same time.

Again, sorry for all the hassle.

Thank you,

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

My apologies for late reply.

Please see your file here.

I have tried to get all the dates

[mail2vjj · Accepted solution]

@Zubair_Muhammad

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

[Zubair_Muhammad]

@mail2vjj

You are welcome.

Following post mentions some good sources for learning

http://community.powerbi.com/t5/Desktop/Power-BI-Learning/m-p/336089#M150393

[mail2vjj]

@Zubair_Muhammad

I already have the Definitive Guide to DAX by Ferarri,Rossi.

It is quite good but it is turning out to be a little too technical for me, when it comes to complex formulas.

I do have the basic understanding of the formulas but I fail miserably when it comes to complex ones.

Off the list that you have mentioned in the link, which book would you recommed, I get first, cause I don't know how technical each one is and you seem to have read them all.

Thank you,

Vishesh Jain

[Zubair_Muhammad · Accepted solution]

Hi @mail2vjj

I think this a very good book.

It is written in a style which is great for learning

Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Exce...

[Zubair_Muhammad]

@mail2vjj

Now you will get the missing dates as well

[Zubair_Muhammad]

@mail2vjj

See file attached here