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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

 

Screenshot 2020-10-12 170910.png

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

 

Cheers

Malte

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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_0-1602603806191.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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_0-1602603806191.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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:

Screenshot 2020-10-14 092641.png

 

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.