Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tonvhemert3
Regular Visitor

Days in stock to zero

Hello,

 

I'm trying to create the following table in PowerBI. It should work as following:

 

 JanFebMarAprMayJunJulAugSepOktNovDec
Starting Inventory20.00025.00035.00025.00035.00040.00045.00055.00045.00025.00045.00035.000
Production10.00020.00015.00030.00040.00050.00040.00030.00040.00040.00030.00025.000
Sales (forecast)5.00010.00025.00020.00035.00045.00030.00040.00060.00020.00040.00030.000
Closing Inventory25.00035.00025.00035.00040.00045.00055.00045.00025.00045.00035.00030.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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

first step is to fix your data model

 

lbendlin_0-1686101199202.png

see if you can take it from there.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

first step is to fix your data model

 

lbendlin_0-1686101199202.png

see if you can take it from there.

Thanks for this!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors