Populate columns - Start time & End Time based on the previous rows end time duration

[Saveesh]

Hi Team,

I am new to Power BI and am currently, working on a Power BI report, where I wanted to create a column - Start date & time based on a given start time, priority, and order from my data table.

I would need to copy the given start time to the first row which has the priority & order 1. 

 1. I am able to do this by having a dynamic table and copying the given start time value from there using the below code 

 table definition : 
_startTimetbl = FIRSTNONBLANK(DraftSource_SartTime[START TIME (24hr)],TRUE())

2. First start time calculation :  

startTime = 

IF(CONTAINSSTRING(_Prioritytbl[Priority] , 1 ) && _Prioritytbl[Order] = 1 , FIRSTNONBLANK(_startTimetbl[DR START TIME],true()))

For the other priorities, when I am writing the logic getting a circular dependency error. 

 Sample code tried : 

PrevValue =
CALCULATE (
   SUM ( _Prioritytbl[endTime] ),
   FILTER (
      _Prioritytbl,
      _Prioritytbl[Index]
           = EARLIER ( _Prioritytbl[Index] ) - 1
           && _Prioritytbl[Priority] = EARLIER ( _Prioritytbl[Priority] )
    )
)

Below is the calculation expected result. 
 

Any help will be highly appreciable. 

Regards,

Saveesh.S

[Saveesh_S]

Thanks for your help.

I would need this data to be represented in a gnat chart and as it is time bounded it not working as expected. Do you have any suggestion on the visuals? 

[Saveesh_S]

This is the sample table created and manually done the calcluation.

Here Procedure  1 , 2 & 3 has the same time because it has the Priority & Order 1 and it is the starting of the calculation. 

For the Procedure 4 - Start time =  Procedure with ourder 1's  Strat time + Max ( Procedure with order 1's Duratoin )
This must continue for all the row items based on the priority and order. 
End time = Start time + Duration(Minutes)

Procedure	Priority	Order	Start time	Duration(Minutes)	End time
Procedure 1	1	1	2023-05-20 10:00	15	2023-05-20 10:15
Procedure 2	1	1	2023-05-20 10:00	45	2023-05-20 10:45
Procedure 3	1	1	2023-05-20 10:00	30	2023-05-20 10:30
Procedure 4	1	2	2023-05-20 10:45	10	2023-05-20 10:55
Procedure 5	1	3	2023-05-20 10:55	30	2023-05-20 11:25
Procedure 6	1	4	2023-05-20 11:25	60	2023-05-20 0:25
Procedure 7	2	1	2023-05-20 0:25	15	2023-05-20 0:40
Procedure 8	2	2		20
Procedure 9	2	3		120
Procedure 10	3	1		90
Procedure 11	3	2		10

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Saveesh_S]

Thanks for the response, I updated the message with table and problem statement. 
Let me know if this works.

[lbendlin · Accepted solution]

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDNCoAgEIRfRTwnrK729xTdo1N5DoLeP9d2oVI6DCzDt7OO86ynY1/jdh5RWd1okQOHBoJxoCyMAGSHwk/W0jwj3H+ELyP8NwL/IxAKP1nvCC/rSTTSygcJjOCNYAVpGfE30laQjo9YPhRKpGeEpPLT8/CGBgaQIVulcg/5HsKGKiW15CLXXy4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Procedure " = _t, Priority = _t, Order = _t, #"Start time" = _t, #"Duration(Minutes)" = _t, #"End time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Procedure ", type text}, {"Priority", Int64.Type}, {"Order", Int64.Type}, {"Start time", type datetime}, {"Duration(Minutes)", Int64.Type}, {"End time", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Priority] = 1) and ([Order] = 1)),
    Correction = List.Sum(#"Filtered Rows"[#"Duration(Minutes)"])-List.Max(#"Filtered Rows"[#"Duration(Minutes)"]),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Priority", Order.Ascending}, {"Order", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    st = #"Added Index"{0}[Start time],
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Start time],each if [Start time] = st then st else st + #duration(0,0,List.Sum(Table.SelectRows(#"Added Index",(k)=>k[Index]<[Index])[#"Duration(Minutes)"])-Correction,0),Replacer.ReplaceValue,{"Start time"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [End time],each [Start time] + #duration(0,0,[#"Duration(Minutes)"],0),Replacer.ReplaceValue,{"End time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Start time", type datetime}, {"End time", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Procedure ", "Priority", "Order", "Start time", "Duration(Minutes)", "End time"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Saveesh_S]

Hi Ibendlin,

When I used the above code, it is just copying the pervious value to the new item, it is not calculating the maximum value of the time from the list above the item.

My code looks like this.

#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Priority] = 0) and ([Order] = 1)),
Correction = List.Sum(#"Filtered Rows"[#"Expected Time (Mins)"])-List.Max(#"Filtered Rows"[#"Expected Time (Mins)"]),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Priority", Order.Ascending}, {"Order", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
st =#"Added Index"{0}[Start Time],

#"Replaced Value_SS" = Table.ReplaceValue(#"Added Index",each [Start Time],each if [Priority] = 0 and [Order] = 1 then st else null,Replacer.ReplaceValue,{"Start Time"}),

#"Replaced Value_Sam" = Table.ReplaceValue(#"Replaced Value_SS",each [Start Time],each if [Start Time] = st then st else st + #duration(0,0,
List.Sum(Table.SelectRows(#"Replaced Value_SS",(k)=>k[Index]<[Index])[#"Expected Time (Mins)"])-Correction,0),Replacer.ReplaceValue,{"Start Time"}),

#"Replaced Value_Sam_1" = Table.ReplaceValue(#"Replaced Value_Sam",each [End Time],each [Start Time] + #duration(0,0,[#"Expected Time (Mins)"],0),Replacer.ReplaceValue,{"End Time"}),

#"Replaced Value_Samxx" = Table.ReplaceValue( #"Replaced Value_Sam_1",each [Start Time],each if [Start Time] = st then st
else List.Max(Table.SelectRows(#"Replaced Value_Sam_1",(k)=>k[Index]<[Index])[#"Replaced Value_Samxx"])
,Replacer.ReplaceValue,{"Start Time"})

as said earlier, I need copy the same start time for the the same order items. Kindly help.