Forum Discussion

steff131284's avatar
steff131284
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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.

steff131284's avatar
steff131284
Frequent Visitor
5 years ago

This works perfectly

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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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.

  • steff131284's avatar
    steff131284
    Frequent Visitor
    5 years ago

    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
    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      5 years ago

      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.

      • steff131284's avatar
        steff131284
        Frequent Visitor
        5 years ago

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