Required Stock carry calculation

[CraigMFuso]

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. 

[Greg_Deckler]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

With Date calendar you have try calculation like this

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))

OR
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))

Stock = round(([Rolling 12],52)*4,0)

[CraigMFuso]

Hi @amitchandak , this calculation needs to be for quanity of units not sales amount. 

I tried to change this to reflect qty how ever it does not calculate correctly. It seems to be a total

[v-lid-msft]

Hi @CraigMFuso ,

We can try to use the following measure to meet your requirement:

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

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.

Best regards,

[CraigMFuso]

Hi @v-lid-msft ,

I have created a simplified sample file for you to review. 

https://fusonz-my.sharepoint.com/:u:/g/personal/craig_murphy_fuso_co_nz/Ec3zj1SfTA1DvJRlzEvGuDYBEYi4...

When applying your suggestion an error occured.

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I simplified the formula as below which returns a correct total value for the table that now only needs to be filtered by customer to show what I need. 

Measure = 
ROUNDUP (
    CALCULATE (
        SUM ( 'Customer Purchase History'[QTY] ),
        'Customer Purchase History'[Trans_Datetime] - 365
    ) / 52 * 4,
    0
)

The measure shown below is a total of the 'customer purchase history' table that I want to be filtered by customer.

This will then show the customers current stock on hand compared to there minimum stock requirement. 

Im new to BI so appreciate the assistance. 

[v-lid-msft · Accepted solution]

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,

[CraigMFuso]

Thanks @v-lid-msft .

Once I changed my relationships by implimenting a basic product table your solution worked great.

[amitchandak]

Can you share sample data and sample output.