Stock On Hand

[jamesdanuk]

Hi,

I am using Power BI (Direct Query) from a SQL Source, and am having problems doing a Closing Stock Balance.

I have in a SQL Table the following structure

Date	Entry Type	Quantity	ItemID	LocationID
31/05/2018	Take On	1000	1	10
01/06/2018	Sales	-10	1	10
01/06/2018	Purchase	5	1	10

I am after a Closing Stock Calculation, that on 01/06/2018 displays for Item 1 and Location 10 the stock, currently when i select Quantity on 01/06/2018 it displays 5, i need it to display 1005 as to take in the closing balance from the beginning rather than limit the scope.

I have a Date Table also that is linked to the Date Column, as well as a Item and Location Dimension that is linked by the ID's on my stock table.

Thanks

Dan

[alexei7]

Hi Dan,

Quick question - is the result you want to see 1005 or 995 (including sales)?

Cheers

Alex

[jamesdanuk]

Hi Alex,

I want 1005 on the 01/06/2018 and 1000 to be returned on the 31/05/2018 does that make sense?

Thanks

Dan

[alexei7]

So sales are not included in your calculation?

Also, what result would you like to see? I'm imagining a visual metric for total sales whereby you filter on one date to see "Closing Stock" for that data, but would be helpful if you can confirm

Thanks

Alex

[jamesdanuk]

Hi Alex,

Apologies it should be 995, not 1005.

Does that help?

In regards to teh Visual i would in the report select a Reporting Date and the Report (Visual will display say bar chart with location or item ) 

Thanks

Dan

[alexei7 · Accepted solution]

Hi Dan,

I think something like the following might help you achieve what you want (although difficult to be certain without knowing your model):

Closing Stock Balance =
CALCULATE (
 SUM ( Table1[Quantity] ),
 FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

Obviously, you'll need to change any table or column names to your own.

Hope that helps,

Thanks

Alex

[Anonymous]

HI @jamesdanuk,

You can add calculate column to replace entry type to 'stock in' and 'stock out', then remove 'data', 'entry type' and add above column to show summary amount.

Regards,

Xiaoxin Sheng

[jamesdanuk]

Hi Xiaoxin,

I am unsure what you mean? are you able to demostrate how to do this ?

Thanks

Dan