cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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 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..

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

2 REPLIES 2
Frequent Visitor

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!

Super User

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors