Forum Discussion
Estimated Start and End Times
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.
Community SupportHi 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.
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)
