Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

    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:

     

     

     

     

    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.

     

     

    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

AndyEagleton's avatar
AndyEagleton
Frequent Visitor
2 years ago

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)

    )