DAX: Calculating next EndTime and next StartTime of an order

[Luukv93]

Hi all,

I have a dataset containing data of our production plant. Each row represents a production order on a production line, either in production (in productie) or planned for production (ingepland).

When an order is in production it has a StartTime, if it is planned it does not have a starttime since it has to wait for the previous order to be completed.

For each order I have calculated the expected throughputtime in hours. Now that we have the starttime of the first order and expected throughput time we can calculate the EndTime for the first order (StartTime + ThroughputTime). Consider the EndTime of the first order the StartTime of the next order when it is on the same production line.

I need your help on creating 2 calculated columns: (Measures do not work since I use the data in a gannt chart)

1 for calculating the EndTime of the orders
1 for calculating the StartTime of the orders (except for the orders in production)

I created an index in Power Query based on the production line and SuborderID in order to use this in the calculated column.

Herebelow some sample data, and the link to wetransfer where I stored the data in excel.

https://we.tl/t-DQRuSZeXf9

Index	ProductionLine	SuborderID	StatusCode	StartTime	Units	ThroughputTime (hours)
1	1	34521	In productie	20-1-2020 13:45	11	0,8
2	1	34522	Ingepland		14	0,3
3	1	34523	Ingepland		14	1,2
4	8	34517	In productie	20-1-2020 13:27	9408	3
5	9	34520	In productie	20-1-2020 14:04	7056	3,5
6	10	34515	In productie	20-1-2020 13:44	2688	2,9

[Melissa]

Hi @Luukv93,

I have a Power Query solution for you, where each row now has a StartTime and EndTime see below.

These are the main steps:

1. Calculated the EndTime for all lines containing a StartTime
2. Duplicated the EndTime and that Filled them Down
3. Calculated the EndTime for the Planned Production
4. Calculated the StartTime for all Planned Production lines

.
And this is the full M code:

let
    Source = data,
    #"Sorted Rows" = Table.Sort(Source,{{"Index", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"StartTime", "StartTime1"}}),
    #"EndDate in Production" = Table.AddColumn(#"Renamed Columns", "EndDate In Production", each if [StartTime1] <> null then [StartTime1] + #duration(0,0,([#"ThroughputTime (hours)"]*60),0) else null, type datetime),
    #"Duplicated EndDate" = Table.DuplicateColumn(#"EndDate in Production", "EndDate In Production", "FilledEndDate"),
    #"Filled Down EndDate" = Table.FillDown(#"Duplicated EndDate",{"FilledEndDate"}),
    TableGroup = Table.Group(#"Filled Down EndDate", {"ProductionLine"}, {{"All Rows", each _, type table [Index=number, ProductionLine=number, SuborderID=number, StatusCode=text, StartTime1=datetime, Units=number, #"ThroughputTime (hours)"=number, EndDate In Production=datetime, FilledEndDate=datetime]}}),
    #"Added Calc" = Table.AddColumn(TableGroup, "Calc", each Table.Skip ([All Rows], 1), type table),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Calc", "All Rows", {"Index", "SuborderID", "StatusCode", "StartTime1", "Units", "ThroughputTime (hours)", "EndDate In Production", "FilledEndDate"}, {"Index", "SuborderID", "StatusCode", "StartTime1", "Units", "ThroughputTime (hours)", "EndDate In Production", "FilledEndDate"}),
    #"Added EndTime" = Table.AddColumn(#"Expanded All Rows", "EndTime", each if [EndDate In Production] <> null then [EndDate In Production] else #duration(0,0, (List.Sum( Table.SelectRows( [Calc], (i) => i[Index] <= Number.From([Index]))[#"ThroughputTime (hours)"])*60 ),0) + [FilledEndDate], type datetime),
    #"Grouped Rows" = Table.Group(#"Added EndTime", {"ProductionLine"}, {{"All Rows", each _, type table [ProductionLine=number, Index=number, SuborderID=number, StatusCode=text, StartTime=datetime, Units=number, #"ThroughputTime (hours)"=number, EndDate In Production=datetime, FilledEndDate=datetime, Calc=table, EndTime=datetime]}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "All Rows", "All Rows 2"),
    #"Expanded All Rows1" = Table.ExpandTableColumn(#"Duplicated Column", "All Rows 2", {"Index", "SuborderID", "StatusCode", "StartTime1", "Units", "ThroughputTime (hours)", "EndTime"}, {"Index", "SuborderID", "StatusCode", "StartTime1", "Units", "ThroughputTime (hours)", "EndTime"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Rows1", "StartTime", each if List.IsEmpty(Table.SelectRows([All Rows], (i) => i[Index] = [Index]-1)[EndTime]) then [StartTime1] else Table.SelectRows([All Rows], (i) => i[Index] = [Index]-1)[EndTime]{0}, type datetime ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ProductionLine", "Index", "SuborderID", "StatusCode", "Units", "ThroughputTime (hours)", "EndTime", "StartTime"})
in
    #"Removed Other Columns"

.
I hope this is helpful.
eDNA - calc End and StartTime PQ.pbix (13.3 KB)

[Luukv93]

Hi @Melissa

Thanks for your effort.
Unfortunately in order to calculate the throughput time there are a lot of calculations involved that I calculated with some DAX.

Could you come up with a calculated column that I can use in a gannt chart?

[Melissa]

Figured since you’d already added the indexing, Power Query might be an option. Apologies for that misunderstanding.

I’ll have another look tonight.
Cheers

[Luukv93]

Thanks Melissa!

[Melissa]

Hi Luuk,

So I’ve created two calculated columns now, the first to determine the EndTime

And the second to calculate the StartTime

Here’s the sample file - I hope this was helpful
eDNA - calc End and StartTime PQ.pbix (44.0 KB)

[Luukv93]

Wonderfull thanks so much!