Calculating Projected Stock

[Anonymous]

Hi all,

I want to be able to extrapolate my inventory into the future by adding incoming goods (Planned and Scheduled) and subtracting expected sales (Forecast Weekly). The most recent stock I have is from the previous Sunday (so, today that would be Oct 11th 2020).

I shared a file with sample data here, so that you can see my data structure and some sample measures. All facts are in one big fact table. In the original data I also have a product and a customer dimension table. But since it is not really relevant for this case, I left it out of the sample data.

I found a couple of forum entries on that topic, but none was really something I could work with. 

Just to make sure, what I would expect is something like this:

It would be much appreciated, if you could hint me in the right direction.

Cheers

Malte

[MFelix · Accepted solution]

hI @Anonymous 

First of all your Datedimension need to be continuous other wise your calculation will no work properly, so you need to unfiltered the dates you have taken out.

I'm assuming that the values that you have for Stock will be in a fixed date in this case you have october 18th then I made the following calculaiton:

m.Stock.PL = 
VAR Stock_Date =
    MAXX (
        FILTER (
            ALL ( FactTable[Date ID Weekly]; FactTable[DataType] );
            FactTable[DataType] = "Stock"
        );
        FactTable[Date ID Weekly]
    )
RETURN
    IF (
        [m.ForecastWeekly.PL] = BLANK ()
            && [m.Scheduled.PL] = BLANK () 
            && [m.Planned.PL] = BLANK ();
        BLANK ();
        CALCULATE (
            SUM ( FactTable[Volume in PL] );
            FactTable[DataType] = "Stock";
            FILTER (
                ALL ( DateDimension[Date ID Weekly] );
                DateDimension[Date ID Weekly] <= SELECTEDVALUE ( DateDimension[Date ID Weekly] )
            )
        )
            - CALCULATE (
                [m.ForecastWeekly.PL] - [m.Scheduled.PL]  - [m.Planned.PL];
                FILTER (
                    ALL ( DateDimension[Date ID Weekly] );
                    DateDimension[Date ID Weekly] <= SELECTEDVALUE ( DateDimension[Date ID Weekly] )
                        && DateDimension[Date ID Weekly] > Stock_Date
                )
            )
    )

Since the planned value is 0 on this case I don't know if you want to sum for the total or subtract you just need to adjust the measure to make the correct calculation.

[Anonymous]

Hi @MFelix , I did a mistake when translating your formula to my original data structure. I also changed the data structure slightly. The forecast from last Sunday is now DataType "Stock Weekly". And now it works. 

Many thanks again for your help!

Cheers

Malte

[Anonymous]

Hi @MFelix  , first of all many thanks for taking the time to investigate on and answer to my post! 

I have tried your formula in my original model (changing +/- for the planned and scheduled; it should be + planned + scheduled - forecast) and the result is this:

After calculating the first value for October 18th without taking the stock from Oct 11th into account, the formula correctly adds planned and scheduled and subtracts the forecast from the projected stock of previous week.

About the date structure in my origional data: I have a date column with the dates of the end of each month plus yesterday. For data that is in weekly buckets, I have the column "Date ID Weekly". The dates in this columns are all Sundays. For stock data, I only have a Date ID Weekly for the stock of last Sunday. So during this week it will always be Oct 11th. This is supposed to be my opening stock for the stock projection. After that I want to add Planned and Scheduled and subtract Forecast Weekly for each week to get to the closing stock of each week.

Do you have an idea, what needs to be changed about the formula to take Oct 11th as the opening stock for week Oct 18th?

Again many thanks for your help. It seems we are very close to a solution.

Cheers

Malte

[MFelix]

Hi @Anonymous ,

Your data add very reduce data so the calculations were based on that value.

What is the column that you are using for the Stock calculation the change should be done in the variable that I place on the top of the measure:

VAR Stock_Date =
    MAXX (
        FILTER (
            ALL ( FactTable[Date ID Weekly]; FactTable[DataType] );
            FactTable[DataType] = "Stock"
        );
        FactTable[Date ID Weekly]
    )

This is picking up the fact table DATE ID on your fact table do you have more than one value of stock or only one date with the stock.