Forum Discussion
Populate columns - Start time & End Time based on the previous rows end time duration
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
_startTimetbl = FIRSTNONBLANK(DraftSource_SartTime[START TIME (24hr)],TRUE())
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
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".
6 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523- lbendlin
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".
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
