Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, I'm new to the forum and I'm Italian so sorry for the bad English.
However, I want to calculate the stock rotation index for the items of my warehouse.
The available tables are:
STK_ID | STK_ITM_ID | STK_WHS_ID | STK_QTY | STK_DATE |
1 | I0001 | WH1 | 40 | 1 Jan 18 |
2 | I0001 | WH1 | 40 | 2 March 18 |
3 | I0001 | WH1 | 37 | 5 June 18 |
4 | I0002 | WH2 | 30 | 2 March 18 |
The table does not contain the stock quantity of each item. Some of them are missing.
STK_ID | MOV_CAU | MOV_ITM_ID | MOV_WHS_ID | MOV_QTY | MOV_DATE |
A1 | Sale | I0001 | WH1 | 12 | 12 Dec 17 |
A2 | Sale | I0001 | WH1 | 24 | 20 Dec 17 |
A3 | Sale | I0001 | WH1 | 3 | 10 April 18 |
A4 | Return | I0001 | WH1 | 10 | 8 Jul 18 |
The update of the stock is not immediate: for example, on 2nd March 2018 the stock level of I0001 was of 40 pieces; in the next month (10th April 2018) 3 pieces were sold and in the next month (5th June 2018) the stock level was updated.
ITM_ID | ITM_GROUP | ITM_CATEGORY |
I0001 | Wine | Blue |
I0002 | Milk | Blue |
I0003 | Beer | Red |
How can I calculate the stock rotation index as total quantity sold divided by average stock?
total quantity sold by what? by month?
Yes, by month
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |