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

[Anonymous]

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 🙂

[Anonymous · Accepted solution]

While the solution is not 'solved' this thread has answered my issue and I'm content that there is no solution. 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Recursion-in-DAX/m-p/2642886/highlight/true#M...

In Short: 
If your forumla has an IF statement and is recursive there is likely no work around. 
If there is no IF statement, you might manage to find a complex mathematical work around.

[Anonymous]

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]

Hi, @Anonymous 

What is your new sample data, max warehouse capacity?

Best Regards

[Anonymous]

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 

[v-zhangti]

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.