Forum Discussion

CraigMFuso's avatar
CraigMFuso
Frequent Visitor
6 years ago
Solved

Required Stock carry calculation

Hi everyone.

Hopefully some one can help. 

 

I am creating a report to show resellers if they are carrying the required level of stock needed. 

 

I need to calculate a minimum stock carry based off purchase history to unsure resellers are carrying the required level of stock.

In my model I have a table which shows transaction history and a table showing customer stock on hand. 

 

Transaction History:

 

 

Customer Stock Holding:

 

 

 

I need to calculate the required minimum stock holding of each product based off purchase history , for each customer, so I can compare current stock to required stock in a visual.

 

The required level of stock is a minimum of 4 weeks based of previous 12 months purchase history.

Example:

  • customer sell product 'x' 60 times in the last 12 months
  • 60 / 52 weeks = 1.15 units per week
  • 1.15 x 4 = 4.6 (round to minimum carry of 5 units)

Once calculated I can show in a visual current stock compared to required stock. 

 

My model is layed out as follows. 

 

Need Help
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi CraigMFuso ,

     

    Sorry for our mistake in the formula, we can try to use the following measure after build some relationship between tables:

     

    Measure = 
var lastUpdate = MAX('Dealer SOH'[Last Updated])
return
ROUNDUP (
    CALCULATE (
        SUM ( 'Customer Purchase History'[QTY] ),
        FILTER('Customer Purchase History',
        'Customer Purchase History'[Trans_Datetime]
            >= lastUpdate - 365
            && 'Customer Purchase History'[Trans_Datetime] < lastUpdate)
    ) / 52 * 4,
    0
)

     

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？


    Best regards,

     

8 Replies