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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
andreaspa
Frequent Visitor

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 table stating the On hand value and another table showing the forecast consuming the inventory every week. 

 

I want to create a leading indicator graph which will allow me to start from the on hand inventory level and remove or add forecast each week. 

 

The idea here is to then imput Safety stock levels or Upper and Lower Control Limits to each code so that we can quickly tell which products we will have problems on.

 

Here are pictures of what i would like as a final stage: 

andreaspa_0-1604069460412.png

ex 385 is the initial inventory, then next week the forecast is netting out 105 units so i will only have 280 on hand and so on and so forth.

 

Here are 2 pictures of the databases that i am trying to extrapolate this info from: Forecast, and On Hand.

Forecast

andreaspa_1-1604069632280.png

 

 

 

On Hand

andreaspa_2-1604069641992.png

 

Ideally i would love to run these 2 reports daily and see what products will go negative or below safety stock in a quickly manner.

 

**Products and quantities are made up for this exercise**

 

Thank you again for your help with this !

 

1 ACCEPTED SOLUTION

Hi @andreaspa 

I update my tables and my measure.

Forecast Table:

1.png

On Hand Table:

2.png

Safety Stock Table:

3.png

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:

4.png

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. 

View solution in original post

5 REPLIES 5
andreaspa
Frequent Visitor

@v-rzhou-msft  Thank you for your help ! 

 

I am now able to calculate what i needed.

The graphing part is very slow but i will try to fix this in other ways.


Thanks again!


Andrea

v-rzhou-msft
Community Support
Community Support

Hi @andreaspa 

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:

1.png

Forecast Table:

2.png

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:

3.png

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

I build a target table.

5.png

Color measure:

 

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

 

Result:

6.png

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. 

Hi @v-rzhou-msft ,


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

Hi @andreaspa 

I update my tables and my measure.

Forecast Table:

1.png

On Hand Table:

2.png

Safety Stock Table:

3.png

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:

4.png

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. 

@v-rzhou-msft 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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.