Forum Discussion

krwalke's avatar
krwalke
Frequent Visitor
3 years ago

Production Schedule - Variable Table for Start Dates in DAX

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 ru...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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])