Forum Discussion
Stock Holding Guide
Hi Guys,
I have been asked to produce a buyers guide, based on product sales & purchases. I have only been doing basic fuctions by using the raw data, but realise that there is a lot more capabilities and ways to achive what I need to provide.
I need to have columns for:
Current stock
last received quantity
Weeks 1 to Week 6 received quantities
Cover ( I assume this will need to be a measure of goods in an goods out over period ? )
Can anyone point me in diretion of any DAX functions that will help me get these values, plus any ideas if anyone else has done this kind of project?
Thanks in advance.
So I have now figured this out tweaking the above details to change from COUNT to SUM and now I have a measure for the Required Cover:
My next issue is to set conditional formatting to highlight the On Hand column to show under and over stocked items.
5 Replies
The data is adirect query, so not easy to send example. I will try and extract some data to provide example.
After speaking with Client again, it seems what they want is:
Count the number of items sold over a 6 week period and divide number by weeks to calculate the required stock level required. So for every item sold over the past 6 weeks, I need to count total items sold and divide by 6 ( to give the recomended stock amount ) I will then have a column to show the 'current' stock holding, and add conditional formatting to hightlight over stocked, under stocked items.
I have tables for the orders that stores orders tblOrders and the items : tblOrderLineItems
Sorry for the vagueness of this questions, Just trying the think how to phrase it?
I have done a basic setup :
This seems to give me the count of each item over the past 6 weeks.
So now need to understand how to add a measure now of the 'count of Qty' column, to give the Recommended Stock holding. So in the case of the Apple - Red it would be just 1 yet for the Apple pack it would be 6.16, so maybe round up the Recommended Stock Holding?
So I have now figured this out tweaking the above details to change from COUNT to SUM and now I have a measure for the Required Cover:
My next issue is to set conditional formatting to highlight the On Hand column to show under and over stocked items.
