Number of Weeks' Stock On Hand

[J_R_Cribb]

I have data which includes Sales Orders, Incoming Orders & Current stock levels for numerous product codes.

Sales Orders and Incoming Orders are assigned to dates of arrival, whilst the current stock level is fixed to today's date.

I have created a measure which calculates the cumulative total of Incoming + Current - Sales, which gives my available stock balance out into the future.

I'd like to calculate an average of the previous 4 weeks' sales, using this to calculate an approximate number of weeks' worth of stock that we have on hand based upon the cumulative total.

Please can you help?

Thank you in advance.

J

[bullius]

Hi @J_R_Cribb

Could you provide an example table of your data?

[J_R_Cribb]

Hi @bullius

Thank you for response, please see below.

Sources:

LC - Incoming Shipped
PO - Incoming Unshipped
SO - Sales Orders

SS - Current Stock

Quantity_2 can be ignored for this exercise.

I'd like to display my report in the below format, with a calculated Week Beginning field as my columns.

I have a separate date table, which has an active relationship with 'Orig_Date' column in my first table.

J

[bullius · Accepted solution]

This will give you the average sales for the past 4 weeks:

Avg Sales (past 4 weeks) = CALCULATE ( AVERAGE ( Table1[Quantity] ), Table1[Source] = "SO", Table1[Orig_Date] >= TODAY() - 28 )

Is that what you need to complete what you are doing?