cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steff131284
Frequent Visitor

Simple inventory development

Hi,

I am totally new to Power BI and have a simple question to show inventory forecast

 

I have:

 

Starting inventory by sku -->

Bestand = SUM(Bestand[Bestand heute])
 
Inventory Increase by month --> 
Zugang = SUM(Produktion[Produktionsmenge SOP])
 
Inventory decrease by month -->
Abgang = SUM(Produktion[S905+Z076 Absatz])
 
Inventory Delta -->
Delta = [Zugang]-[Abgang]
 
Now I need a formula for the inventory forecast by month. I found this one in another discussion: 
Forecast = CALCULATE([Bestand],DATESYTD(DateTable[Date]))+CALCULATE([Delta],DATESYTD(DateTable[Date]))
 
This is working, but just for the current year, for 2022 it is wrong probably because it does not pick up the ending forecast of 2021 anymore  
 
steff131284_0-1618133955376.png

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Community Champion
Community Champion

Hi, @steff131284 

 

Please try the below.

 

Forecast =
CALCULATE (
[Bestand],
FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)
+ CALCULATE (
[Delta],
FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Hi, @steff131284 

Thank you for your feedback.

Is your question, "show the last month ending inventory on this month" ?

Please check the link down below, whether it is what you are looking for.

 

https://www.dropbox.com/s/dugrnjye58e8orh/Reichweiten%20-%20Kopie.pbix?dl=0 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

13 REPLIES 13
steff131284
Frequent Visitor

How can I share it, file type pbix is not supported?

Jihwan_Kim
Community Champion
Community Champion

Hi, @steff131284 

 

Please try the below.

 

Forecast =
CALCULATE (
[Bestand],
FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)
+ CALCULATE (
[Delta],
FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

This works perfectly

Can you explain, what this formula does? I do not understand the syntax 🙂

Hi,

Thank you for your feedback.

The different part is, I think, the below.

 

FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) 

 

The above is working in the CALCULATE function.

1. selects all dates in the date table

2. defines the range of the dates that are less than or equal to the current context date ( = current row's date).

3. Sums up all that are inside the date range defined by the above.

 

It actually does the same job as MTD, QTD, or YTD, but there is no restriction unless you define it by an outside date-slicer.

If you also want not to be restricted by an outside date-slicer, for instance, if you want to sum up from all the way from the beginning (eg. Jan), even if you selected time range from Mar., then you can simply replace ALLSELECTED to ALL.

 

Thank you.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi Kim,

 

I have one follow up question. 

I now what to calculate my Days of stock. 

 

With your formula I calculated the stock at the end of the month.

Now I want to divide this number by the Sales volume of the next month 

 

What do I need to add, so Power BI is using the value for the next month and not for the current month? So far my formula is 

 

Reichweite = DIVIDE([Forecast],[Absatz])*30
 
This gives me not the correct Days of Stock

Hi, @steff131284 

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with an accurate solution.

Thanks.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

How can I share it, file type pbix is not supported?

Hi, @steff131284 

Please save your file in Onedrive or googledrive, and you can share the link here.

Thank you.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi, @steff131284 

Thank you for sharing.

I am not quite sure if I understood your question correctly. I just calculated the next month's sales. Then, created the final measure. Those are the belows.

Please check the link down below as well.

 

Sales Next Month =
VAR nextmonthstartdate =
EOMONTH ( MAX ( DateTable[Date] ), 0 ) + 1
VAR nextmonthenddate =
EOMONTH ( MAX ( DateTable[Date] ), 1 )
VAR nextmonthsales =
CALCULATE (
[Sales],
FILTER (
ALL ( DateTable ),
DateTable[Date] >= nextmonthstartdate
&& DateTable[Date] <= nextmonthenddate
)
)
RETURN
nextmonthsales
 
 
 
Days inventory V2 = DIVIDE([Ending inventory],[Sales Next Month])*30
 
 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

This is perfect, although I have no idea what this formula does 🙂
Last thing. What do I need to do, that also my startig inventory is shown for all months?


Hi, @steff131284 

Thank you for your feedback.

Is your question, "show the last month ending inventory on this month" ?

Please check the link down below, whether it is what you are looking for.

 

https://www.dropbox.com/s/dugrnjye58e8orh/Reichweiten%20-%20Kopie.pbix?dl=0 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Thank you so much

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors