Stock Holding Guide

[lesg]

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.

[lesg]

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? 

[lesg · Accepted solution]

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.

[lesg]

 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?

[lesg]

The data is adirect query, so not easy to send example. I will try and extract some data to provide example.

[adudani]

Hi @lesg 
Please provide sample data and a sample output, removing sensitive data if possible.