Hello,
I'm trying to create the following table in PowerBI. It should work as following:
| | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Okt | Nov | Dec |
| Starting Inventory | 20.000 | 25.000 | 35.000 | 25.000 | 35.000 | 40.000 | 45.000 | 55.000 | 45.000 | 25.000 | 45.000 | 35.000 |
| Production | 10.000 | 20.000 | 15.000 | 30.000 | 40.000 | 50.000 | 40.000 | 30.000 | 40.000 | 40.000 | 30.000 | 25.000 |
| Sales (forecast) | 5.000 | 10.000 | 25.000 | 20.000 | 35.000 | 45.000 | 30.000 | 40.000 | 60.000 | 20.000 | 40.000 | 30.000 |
| Closing Inventory | 25.000 | 35.000 | 25.000 | 35.000 | 40.000 | 45.000 | 55.000 | 45.000 | 25.000 | 45.000 | 35.000 | 30.000 |
| | | | | | | | | | | | | |
| 5. Sales (forecast) per day | | | | | | | | | | | | |
| 6. Days in stock (to zero) | | | | | | | | | | | | |
1. Starting inventory = StockCount[StockCount] if the month is in the past and it should be equal to the Closing Inventory for the future.
2. Production = Production[Production]
3. Sales (forecast) = Sales Actuals[sales(actuals)] if the month is in the past and it should be Sales Forecast[sales(forecast)] if the month is currently active or in the future.
4. Closing Inventory = Starting inventory + Production - Sales (forecast)
5. Sales (forecast) per day = Sales (forecast) / days of that specific month
6. Days in stock (to zero) = Starting inventory / (sales (forecast) per day that month + all months after till 0).
Example for January: 20.000 / (sales all january (5.000) + sales all february (10.000) + sales march (5.000 out of 25.000 in total).
So: 20.000 / sales jan, feb + 1/5 march
I can't share the PBIX file here yet, since I am a fairly new member. However, I can share the file by WeTransfer: https://we.tl/t-OmfofN6WoQ
