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

 

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.

13 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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.