Production Schedule - Variable Table for Start Dates in DAX

[krwalke]

Hi Community,

I've been trying to create a production schedule based on all of the jobs that flow through my facility, taking into account many different parameters (shifts, due dates, time to run through all other equipment, other jobs).  The idea is to develop an algorithm of sorts to prioritize all of the hundreds of jobs and then to set start dates for each job.  I'm quite close with the following DAX code I used from another forum response, but I'm struggling to place a final IF statement.

The Schedule Start Date is my primary output.  The formula works perfectly up until my Ideal Production Date becomes greater than the Schedule Start Date.  In this instance, I'd want to use the Ideal Production Date rather than the Schedule Start Date, but because the Schedule Start Date is iterative, I need that sequence to resume based on the new Ideal Production Date.  In essense, I'm counting up and scheduling the start date based on the running total of Estimate Time - Minutes up until I've caught up, and then I'd prefer to create a gap in the Schedule Start Date rather than starting a job earlier than the Ideal Production Date.  Hope that makes sense..

Please help if you know how best for me to plug in this IF statement!  Thanks!

Schedule Start Date =

    var firststartdate = CALCULATE(MAX('TimeSheetEntries - i300'[Schedule Start]), FILTER('TimeSheetEntries - i300', 'TimeSheetEntries - i300'[Index]=1 && 'TimeSheetEntries - i300'[Printing Finished]<>1 && 'TimeSheetEntries - i300'[Production Plant]="GV")) return

    //var productiondate = IF('TimeSheetEntries - i300'[Ideal Production Date]>firststartdate, 'TimeSheetEntries - i300'[Ideal Production Date], firststartdate) return

    var hours = CALCULATE(SUMX('TimeSheetEntries - i300', 'TimeSheetEntries - i300'[Estimate Time - Minutes]+1), FILTER('TimeSheetEntries - i300', 'TimeSheetEntries - i300'[Index]<EARLIER('TimeSheetEntries - i300'[Index])),'TimeSheetEntries - i300'[Printing Finished]<>1, 'TimeSheetEntries - i300'[Production Plant]="GV")+0 return //filters out printed and Mail Louisville jobs from adding to total minutes

    var _table = TOPN(hours+1, FILTER('Production Gantt', 'Production Gantt'[Value]>firststartdate && ('Production Gantt'[Value]-INT('Production Gantt'[Value]))>Time(6,0,0) && ('Production Gantt'[Value]-INT('Production Gantt'[Value]))<time(22,0,0) && WEEKDAY('Production Gantt'[Value])<>1 && WEEKDAY('Production Gantt'[Value])<>7), [Value], ASC) return //i300 schedule from 6am through 10pm start times on weekdays.

    MAXX(_table, [Value])

[krwalke]

Thanks for the response @amitchandak !  The bad news is that this ended up giving me the same problem, but in reverse where it would correctly predict the dates where 'IdealStartDate' was used rather than 'firststartdate.'  However, I used this as a new column itself and kept my original column, then was able to create a third column to distinguish which of the two to use and it's exactly what I need.  So, this does indeed help!

[amitchandak]

@krwalke , based on what I got, check if this can help

Schedule Start Date =
VAR firststartdate =
    CALCULATE(
        MAX('TimeSheetEntries - i300'[Schedule Start]),
        FILTER(
            'TimeSheetEntries - i300',
            'TimeSheetEntries - i300'[Index] = 1 &&
            'TimeSheetEntries - i300'[Printing Finished] <> 1 &&
            'TimeSheetEntries - i300'[Production Plant] = "GV"
        )
    )
VAR idealProductionDate = 'TimeSheetEntries - i300'[Ideal Production Date]
VAR adjustedStartDate =
    IF(
        idealProductionDate > firststartdate,
        idealProductionDate,
        firststartdate
    )
VAR hours =
    CALCULATE(
        SUMX(
            'TimeSheetEntries - i300',
            'TimeSheetEntries - i300'[Estimate Time - Minutes] + 1
        ),
        FILTER(
            'TimeSheetEntries - i300',
            'TimeSheetEntries - i300'[Index] < EARLIER('TimeSheetEntries - i300'[Index]) &&
            'TimeSheetEntries - i300'[Printing Finished] <> 1 &&
            'TimeSheetEntries - i300'[Production Plant] = "GV"
        )
    ) + 0
VAR _table =
    TOPN(
        hours + 1,
        FILTER(
            'Production Gantt',
            'Production Gantt'[Value] > adjustedStartDate &&
            ('Production Gantt'[Value] - INT('Production Gantt'[Value])) > TIME(6, 0, 0) &&
            ('Production Gantt'[Value] - INT('Production Gantt'[Value])) < TIME(22, 0, 0) &&
            WEEKDAY('Production Gantt'[Value]) <> 1 &&
            WEEKDAY('Production Gantt'[Value]) <> 7
        ),
        [Value],
        ASC
    )
RETURN
    MINX(_table, [Value])