Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative inventory Forecast

Good Morning Everyone!   I am new to power bi and i got stuck on inventory forecasting.   I am having problems forecasting the inventory value remaining by code on a weekly basis.  I have a ta...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I update my tables and my measure.

    Forecast Table:

    On Hand Table:

    Safety Stock Table:

    Measures:

     

    Stock =
VAR _CHANGE =
    SUMX (
        FILTER (
            ALL ( Forecast ),
            Forecast[Planned Dates] <= MAX ( Forecast[Planned Dates] )
                && Forecast[Material] = MAX ( Forecast[Material] )
                && Forecast[Storage Location] = MAX ( Forecast[Storage Location] )
        ),
        Forecast[Quantity]
    )
VAR _Total =
    SUMX (
        FILTER (
            'On Hand',
            'On Hand'[Material] = MAX ( Forecast[Material] )
                && 'On Hand'[Storage Location] = MAX ( 'Forecast'[Storage Location] )
        ),
        'On Hand'[Total Stock]
    )
RETURN
    _CHANGE + _Total
    Color =
IF (
    [Stock]
        >= CALCULATE (
            MAX ( 'Safety stock'[SafeQT] ),
            FILTER (
                'Safety stock',
                'Safety stock'[Material] = MAX ( Forecast[Material] )
                    && 'Safety stock'[Storage Location] = MAX ( 'Forecast'[Storage Location] )
            )
        ),
    1,
    0
)

     

    Result:

    If this reply still couldn't help you solve your problem please show me more details about your tables.

    Here I have some question:

    1. In your On Hand Table: Storage Location for BA000408001 is BA01. However in your Forecast Table, Storage Location is Blank when reqd quantity <0, and Storage Location is IT01 when reqd quantity>0. I couldn't find BA01 in your Forecast Table.

    2. Does each material have the same planned dates like my sample?

     

    You can download the pbix file from this link: Cumulative inventory Forecast

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

I think you want to accumulate forecast on Total stock by each date.

I build two sample table to have a test.

On Hand Table:

Forecast Table:

Measure:

 

Stock = 
VAR _CHANGE = SUMX(FILTER(ALL(Forecast),Forecast[Planned Dates]<=MAX(Forecast[Planned Dates])&&Forecast[Material]=MAX(Forecast[Material])),Forecast[Quantity])
VAR _Total = SUMX(FILTER('On Hand','On Hand'[Material]=MAX(Forecast[Material])),'On Hand'[Total Stock])
Return
_CHANGE+_Total

 

Matrix:

And you can flag the cell in other color if the stock less than safety stock by conditional formmatting.

I build a target table.

Color measure:

 

Color = IF([Stock]>= CALCULATE(MAX('Safety stock'[SafeQT]),FILTER('Safety stock','Safety stock'[Material]=MAX(Forecast[Material]))),1,0)

 

Result:

You can download the pbix file from this link: Cumulative inventory Forecast

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,


    First of all i want to thank you for your help with this!

     

    your answer is very helpful but i don't think it solves the problem in full.


    The dataset i sent you has different storage locations which i will need to be able to filter from in the measure you provided me with called " Stock" .


    Also the dataset has multiple products so the 1 to many relationships that you provided me with for the connections between on Hand table and Forecast table won't work on this.


    Let me know if you know how to fix this, 

     

    either way thank you so much for your super helpful tips!


    Best,

    Andrea

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous 

      I update my tables and my measure.

      Forecast Table:

      On Hand Table:

      Safety Stock Table:

      Measures:

       

      Stock =
VAR _CHANGE =
    SUMX (
        FILTER (
            ALL ( Forecast ),
            Forecast[Planned Dates] <= MAX ( Forecast[Planned Dates] )
                && Forecast[Material] = MAX ( Forecast[Material] )
                && Forecast[Storage Location] = MAX ( Forecast[Storage Location] )
        ),
        Forecast[Quantity]
    )
VAR _Total =
    SUMX (
        FILTER (
            'On Hand',
            'On Hand'[Material] = MAX ( Forecast[Material] )
                && 'On Hand'[Storage Location] = MAX ( 'Forecast'[Storage Location] )
        ),
        'On Hand'[Total Stock]
    )
RETURN
    _CHANGE + _Total
      Color =
IF (
    [Stock]
        >= CALCULATE (
            MAX ( 'Safety stock'[SafeQT] ),
            FILTER (
                'Safety stock',
                'Safety stock'[Material] = MAX ( Forecast[Material] )
                    && 'Safety stock'[Storage Location] = MAX ( 'Forecast'[Storage Location] )
            )
        ),
    1,
    0
)

       

      Result:

      If this reply still couldn't help you solve your problem please show me more details about your tables.

      Here I have some question:

      1. In your On Hand Table: Storage Location for BA000408001 is BA01. However in your Forecast Table, Storage Location is Blank when reqd quantity <0, and Storage Location is IT01 when reqd quantity>0. I couldn't find BA01 in your Forecast Table.

      2. Does each material have the same planned dates like my sample?

       

      You can download the pbix file from this link: Cumulative inventory Forecast

       

      Best Regards,

      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

      • jakeudy's avatar
        jakeudy
        Helper I
        5 years ago

        Anonymous I am having the same problem as the OP, however my planned dates are all different. They aren't the same for each type of material. How can I alter the "Stock" measure to account for this?