Estimated Start and End Times

[iabramson]

Hello, I am trying to create a schedule tool that will estimate the start and end times of diffrent steps in a manuifacturing sequence. As the step gets completed data will come in to the start time and end time columns in my table and it will indicate which step is in progress and which are future steps.

How do I then take the last time in the sequence and use that to estimate start times for the other steps.  For example (see below) current step is step 9:

Step 9's end time will be the start time + target time to give an end time (since its in progress).

Step 10 start time will = step 9 end time and step 10 end time will be step 10 start time + step 10 target time.

Step 11 start time will = step 10 end time ; step 11 end time will be step 11 start time + step 11 target time this cycle will repeat until step 14......

The cycle will then be reapted over again for type 2 (step 1 type 2 est start time will be step 14 type 1 end time... step 1 type 2 end time will be step 1 type 2 start time + target)

SeqStepStart TimeEnd TimeIn ProgressFutureTypeTarget TimeDurationEstimated end in progressEstimated Start Timestart

1	Sterilize	5/7/2025 7:47:15 PM	5/7/2025 8:30:30 PM	0	0	1	0.8	0.720833333325572			#ERROR
2	Idle Dry Ster	5/7/2025 8:30:30 PM	5/7/2025 8:47:00 PM	0	0	1	0.5	0.275000000023283			#ERROR
3	Media	5/7/2025 8:47:00 PM	5/7/2025 9:21:14 PM	0	0	1	0.53	0.570555555517785			#ERROR
4	Media Done	5/7/2025 9:21:14 PM	5/7/2025 9:22:59 PM	0	0	1	0.2	0.0291666666744277			#ERROR
5	Bster	5/7/2025 9:22:59 PM	5/7/2025 11:43:15 PM	0	0	1	2.32	2.33777777786599			#ERROR
6	Idle Ster	5/7/2025 11:43:15 PM	5/8/2025 1:19:29 AM	0	0	1	0.5	1.60388888890157			#ERROR
7	Pre-Inoc	5/8/2025 1:19:29 AM	5/8/2025 1:30:59 AM	0	0	1	0.5	0.191666666592937			#ERROR
8	Ferment	5/8/2025 1:30:59 AM		1	0	1	36	13.8611592592788	5/9/2025 1:30:59 PM		#ERROR
9	Harvest			0	1	1	1.25				#ERROR
10	Centrifuging			0	1	1	1.5				#ERROR
11	Idle Dirty			0	1	1	1.25				#ERROR
12	CIP			0	1	1	1.15				#ERROR
13	Idle Clean			0	1	1	0.25				#ERROR
14	Citric			0	1	1	0.85				#ERROR
1	Sterilize			0	1	2	0.8				#ERROR
2	Idle Dry Ster			0	1	2	0.5				#ERROR
3	Media			0	1	2	0.53				#ERROR
4	Media Done			0	1	2	0.2				#ERROR
5	Bster			0	1	2	2.32				#ERROR
6	Idle Ster			0	1	2	0.5				#ERROR
7	Pre-Inoc			0	1	2	0.5				#ERROR
8	Ferment			0	1	2	36				#ERROR
9	Harvest			0	1	2	1.25				#ERROR
10	Centrifuging			0	1	2	1.5				#ERROR
11	Idle Dirty			0	1	2	1.25				#ERROR
12	CIP			0	1	2	1.15				#ERROR
13	Idle Clean			0	1	2	0.25				#ERROR
14	Citric			0	1	2	0.85				#ERROR

[v-venuppu]

Hi @iabramson ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @iabramson ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @iabramson ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

Hi @iabramson ,

Yes, you can implement this using DAX calculated columns, especially since your Target Time values are stored in a separate lookup table. Here's how you can achieve it:

1.Your main table (StepsTable, for example) should have a relationship to the TargetTimeLookup table on [Step] or [Seq].

2.Create Calculated Columns:

a.Estimated Start Time =
VAR CurrentType = StepsTable[Type]
VAR CurrentSeq = StepsTable[Seq]
VAR PrevStepEndTime =
CALCULATE(
MAX(StepsTable[Estimated End Time]),
FILTER(StepsTable,
StepsTable[Type] = CurrentType &&
StepsTable[Seq] = CurrentSeq - 1
)
)
RETURN
IF(StepsTable[Future] = 1,
PrevStepEndTime,
BLANK()
)

b.Estimated End Time =
VAR StartTime = StepsTable[Estimated Start Time]
VAR TargetTime =
LOOKUPVALUE(
TargetTimeLookup[Target Time],
TargetTimeLookup[Step], StepsTable[Step]
)
RETURN
IF(StepsTable[Future] = 1,
StartTime + (TargetTime / 24),
BLANK()
)

• Make sure your Target Time is in hours, hence dividing by 24.

This logic naturally resets at each Type since we filter by [Type] = CurrentType.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @iabramson ,

Thank you for reaching out to Microsoft Fabric Community.

I’ve prepared a Power BI solution that implements your manufacturing sequence estimation logic using Power Query (M code). 

Please find the attached .pbix file for your reference.

The attached .pbix file includes:

1.Dynamic calculation of Estimated Start Time and Estimated End Time for steps where Future = 1.
2.Uses the last available End Time (or calculated estimate) as the reference for the next step.
3.Supports in-progress steps using actual Start Time if available.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[iabramson]

Is there a way to do this outside of power query using a calculated column? my target times live in a lookup table (outside of power query)