Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculating a Production Plan Measure ( Recursive work-around needed )

Hi

Long time lurker, I appreciate all the help that goes in these forums, it's helped me immensly in the past. 
But I've reached a point that I'm stuck. 
I'm hoping someone can look at the work I've done and figure out the final step I'm missing.
I'm at the point of thinking it's not possible, but I hope that's not the case.

Below is a snapshot of what I'm trying to achive.
In short, there are ordered pieces, and what a warehouse is capable of fullfilling. 
Any over capacity needs to be moved forward onto the next available date.


Both the excel and pbix file with a sumamry of everything I've managed to do so far: Drive folder 
The excel explains the issue, and shows the logic used in the PBIX on a cell ref level. 
(Might help some of you to understand what I've done. )

Some important information:
1.) All inputs are calculated measures; both the ordered pcs and max warehouse capacity are measures.
(These values are calculated from user inputs on warehouse_code, date and product type, and therefore a calculated column will not work. I would have liked to use EARLIER.)
2.) I've tried using boolean conditions to set ranges that I can itterate over, but I can't seem to cover all situations and I'm not sure how to get around it. 

 

1.) BooleanCondition = 
IF ( 
    OrderedPieces[Ordered Pieces] > 'Maximum Warehouse Capacity'[Maximum Warehouse Capacity Value], 
    TRUE(), 
    FALSE()
)
2.) Over Capacity = 
IF ( NOT ISBLANK ( OrderedPieces[Ordered Pieces] ),
    VAR RefDate = MAX ( DateTable_Day[Date] )
    VAR DatesBefore = 
        FILTER ( ALL ( DateTable_Day[Date] ),
            DateTable_Day[Date] <= RefDate
        )
    VAR DatesToCompute = FILTER( DatesBefore, [1.) BooleanCondition] = FALSE() )
    VAR LastCompute = MAXX ( DatesToCompute, DateTable_Day[Date] )
    VAR FirstDateEver = CALCULATE( MIN ( DateTable_Day[Date] ), REMOVEFILTERS() )
    VAR MaxFalseDate = COALESCE( LastCompute, FirstDateEver )

    VAR SumofOrderedPcs = 
        CALCULATE( 
            OrderedPieces[Ordered Pieces],
            DATESBETWEEN ( DateTable_Day[Date], MaxFalseDate + 1, RefDate )
        )
    VAR DaysOfOverCapacity =
        CALCULATE(
            COUNTROWS ( DateTable_Day ) * 'Maximum Warehouse Capacity'[Maximum Warehouse Capacity Value],
            DATESBETWEEN( DateTable_Day[Date], MaxFalseDate + 1, RefDate )
        )
    VAR FalseValue = SumofOrderedPcs - DaysOfOverCapacity
    
    RETURN 
        FalseValue
)
3.) UnderCapacity = 
IF( NOT ISBLANK( OrderedPieces[Ordered Pieces] ),
    VAR RefDate = MAX ( DateTable_Day[Date] )
    VAR DatesBefore = 
        FILTER ( 
            ALL ( DateTable_Day[Date] ),
            DateTable_Day[Date] <= RefDate
        )
    VAR FalseDatesToCompute = FILTER( DatesBefore, [1.) BooleanCondition] = FALSE() )
    VAR LastFalseCompute = MAXX ( FalseDatesToCompute, DateTable_Day[Date] )
    VAR FirstDateEver = CALCULATE( MIN ( DateTable_Day[Date] ), REMOVEFILTERS() )
    VAR MaxFalseDate = COALESCE( LastFalseCompute, FirstDateEver )
    VAR PreviousMaxFalseDate = 
        MINX (
            TOPN ( 2, FalseDatesToCompute, DateTable_Day[Date] ),
            DateTable_Day[Date]
        )
    VAR PreSumofOrderedPcs = 
        CALCULATE( 
            OrderedPieces[Ordered Pieces],
            DateTable_Day[Date] = MaxFalseDate
        )
    VAR PrePreSumofOrderedPcs = 
        CALCULATE( 
            OrderedPieces[Ordered Pieces],
            DATESBETWEEN ( DateTable_Day[Date], PreviousMaxFalseDate + 1, MaxFalseDate - 1 )
        )
    VAR PreDaysOfOverCapacity =
        CALCULATE(
            COUNTROWS ( DateTable_Day ) * 'Maximum Warehouse Capacity'[Maximum Warehouse Capacity Value],
            DATESBETWEEN ( DateTable_Day[Date], PreviousMaxFalseDate + 1, MaxFalseDate - 1 )
        )
    VAR PreFalseValue =
        IF ( 
            PreSumofOrderedPcs + (PrePreSumofOrderedPcs - PreDaysOfOverCapacity) > 'Maximum Warehouse Capacity'[Maximum Warehouse Capacity Value],
            PreSumofOrderedPcs + (PrePreSumofOrderedPcs - PreDaysOfOverCapacity) - 'Maximum Warehouse Capacity'[Maximum Warehouse Capacity Value],
            0
        )

    RETURN 
    PreFalseValue
)
4.) Backlog = [2.) Over Capacity] + [3.) UnderCapacity]

 


Any help will be appraciated!
I hope this is a tough one, that's not easily solved 🙂

5 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support

    Hi, Anonymous 

     

    I solved this problem with multiple measures. I put all the process formulas, you can try to merge some formulas later.

    Difference = IF([Ordered Pieces]-[Max warehouse capacity]<0,0,[Ordered Pieces]-[Max warehouse capacity])
    PreDiff = 
    Var _PreDate=MAXX(FILTER(ALL('Table'[Date]),'Table'[Date]<SELECTEDVALUE('Table'[Date])),[Date])
    Return
    CALCULATE([Difference],FILTER(ALL('Table'),[Date]=_PreDate))
    Measure = IF([Ordered Pieces]>[Max warehouse capacity],[Max warehouse capacity],[Ordered Pieces]+[PreDiff])

    This is just the beginning.

    Measure 2 = IF([Measure]>[Max warehouse capacity],[Measure]-[Max warehouse capacity])
    Measure 3 = 
    Var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Measure 2]<>BLANK()))
    Return
    IF(SELECTEDVALUE('Table'[Date])>_mindate,[Max warehouse capacity]-[Measure])
    Measure 4 = SUMX(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Measure 3])
    Measure 5 = 
    Var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Measure 4]>[Max warehouse capacity]))
    Var _minvalue=CALCULATE([Measure 4],FILTER(ALL('Table'),[Date]=_mindate))
    Return
    CALCULATE([Measure 2],FILTER(ALL('Table'),[Measure 2]<>BLANK()))-_minvalue
    Production Plan = 
    Var _Date1=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Measure 2]<>BLANK()))
    Var _Date2=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Measure 4]>[Max warehouse capacity]))
    Return
    SWITCH(TRUE(),
    SELECTEDVALUE('Table'[Date])>=_Date1&&SELECTEDVALUE('Table'[Date])<=_Date2,[Max warehouse capacity],
    SELECTEDVALUE('Table'[Date])=_Date2+1,[Measure]+[Measure 5],
    [Measure])

    Result:

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi, 
    I love your different approach. I'm going to dig into it more later. 
    It's not quite working, the production plan is not 100%, but it feels like there is something here!
    Thanks 🙂

     

    • v-zhangti's avatar
      v-zhangti
      Community Support

      Hi, Anonymous 

       

      What is your new sample data, max warehouse capacity?

       

      Best Regards

      • Anonymous's avatar
        Anonymous
        Not applicable

        It's the scenario data I attached in both the excel and powerbi drive link. 
        The image I took was from real data. 
        The scenario data, has added complexity to ensure we cover all possible events. 
        The scenario data can be found here: Google Drive Link