Days in stock to zero

[tonvhemert3]

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

[lbendlin · Accepted solution]

first step is to fix your data model

see if you can take it from there.

[tonvhemert3]

Thanks for this!