Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculating Projected Stock

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

    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.

     

MFelix's avatar
MFelix
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

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

      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.