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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Iterative Stock Cover Calculation

Hi all,
There is a very complicated situation i am trying to solve. Actually i tried a lot of thing but i couldn't find a solution yet.
If someone have any idea to solve this please let me know.
I want to make stock coverage calculation.
I have a opening value, i calculated it from another measure.
The opening value is 100. I would look at how much I could cover according to the sales forecast data if there was no production. For the first week, our Sales Forecast Value is  50, and 60 for the second week. I can cover 50 stock up in first week and  50/60 for second week. In this case, the coverage value for the first week will be 1 + 5/6. When I went to the second week, my new stock will be 90 with the calculation of 100 + 40-50 since I made 40 production and made 50 sales in the previous week. I will do the same calculation for this week's coverage calculation. My stock is 90. The forecast for the second week is 60, which can cover the first week. my sales forecast for the third week is 30, I can cover this week. but from now on, I can't cover the next week because my stock is over. The coverage value for this week will be 2 with 1 + 1 calculation. With this calculation method, the coverage value for the 3rd week is 1.5 with the calculation of 1 + 1/2. My last week coverage will be 3/4.

I'm  sharing example data set for my case.
*The bold values are coming from my table, others were calculated


                  Opening Value           ProductionPlan           SalesForecast       Cover
w1                      100                                40                             50                 1.83
w2                        90                                 20                             60                   2
w3                        50                                 10                             30                  1.5
w4                        30                                 0                               40                 0.75

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @Anonymous

 

Here is an example pbix showing one way to do the calculations.

PBIX link

 

I put a table in the PBIX showing the key figures:image.png

 

 

 

 

Generally, when you have Sales/Production data and you want to derive balances at any point in time, you need to create a series of cumulative calculations. 

 

In this case I started with a table pretty much as you described. Opening Value exists only in the first week.image.png

 

 

Then created a set of base measures (Opening, Production, Sales) and corresponding cumulative measures.

Opening = 
SUM ( Forecast[Opening Value] )

Production = 
SUM ( Forecast[ProductionPlan] )

Sales = 
SUM ( Forecast[SalesForecast] )

Cumulative Opening = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Opening], Forecast[Week] <= MaxWeek + 1 )

Cumulative Production = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Production], Forecast[Week] <= MaxWeek )

Cumulative Sales = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Sales], Forecast[Week] <= MaxWeek )

Then created Closing/Opening balance measures, representing "stock" on hand at the end/start of each week:

Balance Closing = 
[Cumulative Opening] + [Cumulative Production] - [Cumulative Sales]

Balance Opening = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Balance Closing], Forecast[Week] = MaxWeek - 1 )

Then the Cover measure makes use of the above measures.

Cover =
VAR BalanceOpening = [Balance Opening]
VAR MaxWeek =
    MAX ( Forecast[Week] )
VAR FutureWeeks =
    FILTER ( ALL ( Forecast[Week] ), Forecast[Week] >= MaxWeek )
VAR PreviousSalesCumulative =
    CALCULATE ( [Cumulative Sales], Forecast[Week] < MaxWeek )
VAR FutureSales =
    GENERATE (
        FutureWeeks,
        VAR SalesInWeek = [Sales]
        VAR FutureSalesCumulative = [Cumulative Sales] - PreviousSalesCumulative
        VAR FutureSalesCumulativePre = FutureSalesCumulative - SalesInWeek
        RETURN
            ROW ( "WeekIndex", Forecast[Week] - MaxWeek,
            "SalesInweek", SalesInWeek,
            "FutureCumulativeSales", FutureSalesCumulative,
            "FutureCumulativeSalesPre", FutureSalesCumulativePre )
    )
VAR CoverResult =
    MINX (
        FutureSales,
        IF (
            [FutureCumulativeSales] >= BalanceOpening
                && [FutureCumulativeSales] - [SalesInweek]
                <= BalanceOpening,
            [WeekIndex]
                + DIVIDE ( BalanceOpening - [FutureCumulativeSalesPre], [SalesInWeek] )
        )
    )
RETURN
    CoverResult

This particular measure finds the earliest week where the Opening Balance is exhausted by future sales, then calculates the fractional week index where this occurs. The FutureSales variable stores the key values used in the calculation, then CoverResult finds the appropriate row and does the final calculation (using MINX). The above code could well be improved!

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

12 REPLIES 12
AndyEagleton
Frequent Visitor

For what it is worth I have an alternative solution to this. Conceptually it's similar in that it calculates a table for the future then isolates the row in the table at which the stock out occurs. But the implementation is different:

 

Cover = 

VAR CurrentInventory = [Opening]

VAR CurrentWeek = SELECTEDVALUE(Dates[WeekCounter])

VAR FollowingWeeks = WINDOW(0, REL, -1, ABS, CALCULATETABLE(VALUES(Dates[WeekCounter]), ALLSELECTED(Dates)))

VAR Table1 = CALCULATETABLE(ADDCOLUMNS(FollowingWeeks, "@Forecast", [Forecast]), ALLSELECTED(Dates))

VAR Table2 = ADDCOLUMNS(
		Table1,
		"@CumForecast", 
		SUMX(WINDOW(0, ABS, 0, REL, Table1, ORDERBY([WeekCounter])), [@Forecast])
)

VAR StockOutRow = INDEX(1, FILTER(Table2, [@CumForecast] >= CurrentInventory))

VAR WholeCoverage = SELECTCOLUMNS(StockOutRow, [WeekCounter]) - CurrentWeek

VAR UnreachableCumForcast = SELECTCOLUMNS(StockOutRow, [@CumForecast])

RETURN 

    IF(NOT(ISBLANK(UnreachableCumForcast)),

        VAR QuantityShort = UnreachableCumForcast - CurrentInventory

        VAR UnreachableForecast = SELECTCOLUMNS(StockOutRow, [@Forecast]) 

        RETURN WholeCoverage + DIVIDE(UnreachableForecast - QuantityShort, UnreachableForecast)

    )
Joshi_M
Helper I
Helper I

Hi Owen,

May I connect with you regarding a similar scenario which i am struggling with.

Let me know

Regards

Joshi_M

 

Hi @Joshi_M 

Sure, I can try to take a look when I have a chance.

I will send you contact details.

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi there,

Thanks for this, it helped me solve a problem on my end.  Now i am faced with a new issue, i want to export the results of these measures into a table.  Every time I use the SUMMARIZE function the iteration breaks.  Ahy help on how to extract the visualized table results into another table?

@Anonymous - can you clarify what you're trying to do, and provide sample data from your model? If you want to create a calculated table that groups by certain columns and adds a column containing a measure, you should be able to use SUMMARIZECOLUMNS or ADDCOLUMNS(SUMMARIZE(...),...).


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi Owen, thanks for responding.  I started my own thread on this here:

 

https://community.powerbi.com/t5/Desktop/Extracting-iterative-calculations-in-visualization-to-new-t...

 

i will add sample data here shortly. Thanks for any help you can provide!

 

@OwenAuger 

OwenAuger
Super User
Super User

Hi @Anonymous

 

Here is an example pbix showing one way to do the calculations.

PBIX link

 

I put a table in the PBIX showing the key figures:image.png

 

 

 

 

Generally, when you have Sales/Production data and you want to derive balances at any point in time, you need to create a series of cumulative calculations. 

 

In this case I started with a table pretty much as you described. Opening Value exists only in the first week.image.png

 

 

Then created a set of base measures (Opening, Production, Sales) and corresponding cumulative measures.

Opening = 
SUM ( Forecast[Opening Value] )

Production = 
SUM ( Forecast[ProductionPlan] )

Sales = 
SUM ( Forecast[SalesForecast] )

Cumulative Opening = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Opening], Forecast[Week] <= MaxWeek + 1 )

Cumulative Production = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Production], Forecast[Week] <= MaxWeek )

Cumulative Sales = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Sales], Forecast[Week] <= MaxWeek )

Then created Closing/Opening balance measures, representing "stock" on hand at the end/start of each week:

Balance Closing = 
[Cumulative Opening] + [Cumulative Production] - [Cumulative Sales]

Balance Opening = 
VAR MaxWeek =
    MAX ( Forecast[Week] )
RETURN
    CALCULATE ( [Balance Closing], Forecast[Week] = MaxWeek - 1 )

Then the Cover measure makes use of the above measures.

Cover =
VAR BalanceOpening = [Balance Opening]
VAR MaxWeek =
    MAX ( Forecast[Week] )
VAR FutureWeeks =
    FILTER ( ALL ( Forecast[Week] ), Forecast[Week] >= MaxWeek )
VAR PreviousSalesCumulative =
    CALCULATE ( [Cumulative Sales], Forecast[Week] < MaxWeek )
VAR FutureSales =
    GENERATE (
        FutureWeeks,
        VAR SalesInWeek = [Sales]
        VAR FutureSalesCumulative = [Cumulative Sales] - PreviousSalesCumulative
        VAR FutureSalesCumulativePre = FutureSalesCumulative - SalesInWeek
        RETURN
            ROW ( "WeekIndex", Forecast[Week] - MaxWeek,
            "SalesInweek", SalesInWeek,
            "FutureCumulativeSales", FutureSalesCumulative,
            "FutureCumulativeSalesPre", FutureSalesCumulativePre )
    )
VAR CoverResult =
    MINX (
        FutureSales,
        IF (
            [FutureCumulativeSales] >= BalanceOpening
                && [FutureCumulativeSales] - [SalesInweek]
                <= BalanceOpening,
            [WeekIndex]
                + DIVIDE ( BalanceOpening - [FutureCumulativeSalesPre], [SalesInWeek] )
        )
    )
RETURN
    CoverResult

This particular measure finds the earliest week where the Opening Balance is exhausted by future sales, then calculates the fractional week index where this occurs. The FutureSales variable stores the key values used in the calculation, then CoverResult finds the appropriate row and does the final calculation (using MINX). The above code could well be improved!

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger ,

 

I really like your solution and think it is the most complete one I have seen so far.

 

One thing where I need some help is how to display the Date instead of the Week# when showing the measure. I want to display the Cover in a chart for example with the x-axis shoing the real date (and not week number) and y-axis showing the coverage. May be it is obivious, but I for one could not figure out a easy way to do this...

 

How would you solve it?

 

Cheers, Avi

Anonymous
Not applicable

Hello @OwenAuger

 

For this data set it's working fine, but when my data set change cover calculation breakes.

If the Balance Opening Valeu is greater than cumulative sales, it doesn't calculate cover measure.

I'm sharing PBIX Link for this dataset.

Thanks for your helps.

@Anonymous

Thanks for pointing out this issue.

 

The situation you have highlighted is that when the Opening Balance is so high that it is never exhausted by forecast sales, my measure can't find a week where it is exhausted, and returns blank.

 

In this situation, it might make more sense to return the number of weeks remaining, because the Opening Balance will cover at least those weeks.

 

I have updated my file with this measure, and changed the Opening value to 190 as in your latest example.

 

Cover = 
VAR BalanceOpening = [Balance Opening]
VAR MaxWeek =
    MAX ( Forecast[Week] )
VAR FutureWeeks =
    FILTER ( ALL ( Forecast[Week] ), Forecast[Week] >= MaxWeek )
VAR PreviousSalesCumulative =
    CALCULATE ( [Cumulative Sales], Forecast[Week] < MaxWeek )
VAR FutureSales =
    GENERATE (
        FutureWeeks,
        VAR SalesInWeek = [Sales]
        VAR FutureSalesCumulative = [Cumulative Sales] - PreviousSalesCumulative
        VAR FutureSalesCumulativePre = FutureSalesCumulative - SalesInWeek
        RETURN
            ROW ( "WeekIndex", Forecast[Week] - MaxWeek,
            "SalesInweek", SalesInWeek,
            "FutureCumulativeSales", FutureSalesCumulative,
            "FutureCumulativeSalesPre", FutureSalesCumulativePre )
    )
VAR CoverResult =
    MINX (
        FutureSales,
        IF (
            [FutureCumulativeSales] >= BalanceOpening
                && [FutureCumulativeSales] - [SalesInweek]
                <= BalanceOpening,
            [WeekIndex]
                + DIVIDE ( BalanceOpening - [FutureCumulativeSalesPre], [SalesInWeek] )
        )
    )
VAR WeeksRemaining = MAXX ( FutureSales, [WeekIndex] ) + 1
RETURN
    IF ( NOT ISBLANK ( CoverResult ), CoverResult, WeeksRemaining )

Does this make sense as an alternative, or would you want to do something different?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi. I am applying this solution to my work. But the issue I have at the moment is that when the current period closing balance is negative the opening balance is also negative which might not reflect the actual picture. Say you ending with -100 stock  that means you were out of stock and didnt cover the sales in this period but the opening bal for the next period should be 0 and any production coming in from there will be used to cover the next sales (instead of accounted for the sales not covered in previous period). How can I apply that to this case? Thanks 

You need to zero out the sales in the buckets where your inventory dips below zero so that it does not contribute to cumulative sales. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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