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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

13 REPLIES 13
steff131284
Frequent Visitor

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

Jihwan_Kim
Super User
Super User

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you so much

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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